操作和访问数据库
数据库连接被用于向数据库服务器发送命令和 SQL 语句,并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式:
-
Statement:用于执行静态 SQL 语句并返回它所生成结果的对象。
-
PrepatedStatement:SQL语句被预编译并存储在此对象中,可以使用此对象多次高效地执行该语句。
-
CallableStatement:用于执行 SQL 存储过程。
使用Statement操作数据表的弊端
通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于执行静态的 SQL 语句,并且返回执行结果。
Statement 接口中定义了下列方法用于执行 SQL 语句:
int excuteUpdate(String sql):执行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql):执行查询操作SELECT
但是使用Statement操作数据表存在弊端:
-
问题一:存在拼串操作,繁琐
-
问题二:存在SQL注入问题
SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的 SQL 语句段或命令(如:SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1'
),从而利用系统的 SQL 引擎完成恶意行为的做法。
对于 Java 而言,要防范 SQL 注入,只要用 PreparedStatement(从Statement扩展而来)取代 Statement 就可以了。
代码演示:
public class StatementTest {
// 使用Statement的弊端:需要拼写sql语句,并且存在SQL注入的问题
@Test
public void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("用户名:");
String userName = scan.nextLine();
System.out.print("密 码:");
String password = scan.nextLine();
// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
+ "'";
User user = get(sql, User.class);
if (user != null) {
System.out.println("登陆成功!");
} else {
System.out.println("用户名或密码错误!");
}
}
// 使用Statement实现对数据表的查询操作
public <T> T get(String sql, Class<T> clazz) {
T t = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
// 2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 3.加载驱动
Class.forName(driverClass);
// 4.获取连接
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
rs = st.executeQuery(sql);
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next())
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// //1. 获取列的名称
// String columnName = rsmd.getColumnName(i+1);
// 1. 获取列的别名
String columnName = rsmd.getColumnLabel(i + 1);
// 2. 根据列名获取对应数据表中的数据
Object columnVal = rs.getObject(columnName);
// 3. 将数据表中得到的数据,封装进对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
所以需要使用PreparedStatement替换Statement。
PreparedStatement的使用
PreparedStatement介绍
可以通过调用 Connection 对象的 preparedStatement(String sql)
方法获取 PreparedStatement 对象
PreparedStatement 接口是 Statement 的子接口,它表示一条预编译过的 SQL 语句
PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示,调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数,第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始),第二个是设置的 SQL 语句中的参数的值。
PreparedStatement vs Statement
代码的可读性和可维护性。
PreparedStatement 能最大可能提高性能:
-
DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用,所以语句在被DBServer的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中就会得到执行。
-
在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
-
(语法检查,语义检查,翻译成二进制命令,缓存)
-
PreparedStatement 可以防止 SQL 注入。
Java与SQL对应数据类型转换表
Java类型 | SQL类型 |
---|---|
boolean | BIT |
byte | TINYINT |
short | SMALLINT |
int | INTEGER |
long | BIGINT |
String | CHAR,VARCHAR,LONGVARCHAR |
byte array | BINARY , VAR BINARY |
java.sql.Date | DATE |
java.sql.Time | TIME |
java.sql.Timestamp | TIMESTAMP |
使用PreparedStatement实现CRUD操作
package com.coydone;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
import java.util.Scanner;
//占位符的方式crud
public class Test03 {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
@Before
public void beforeMethod(){
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名", "root", "root123");
} catch (Exception e) {
e.printStackTrace();
}
}
@After
public void afterMethod(){
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//添加数据
@Test
public void addEmp(){
Scanner input = new Scanner(System.in);
System.out.print("请输入要添加的员工姓名:");
String ename = input.next();
System.out.print("请输入要添加的员工工作:");
String job = input.next();
System.out.print("请输入要添加的上级编号:");
String mgr = input.next();
System.out.print("请输入要添加的入职日期:");
String hiredate = input.next();
System.out.print("请输入要添加的员工工资:");
String sal = input.next();
System.out.print("请输入要添加的员工奖金:");
String comm = input.next();
System.out.print("请输入要添加的员工部门编号:");
String deptno = input.next();
try {
String sql = "INSERT INTO employee (ename,job,mgr,hiredate,sal,comm,deptno) VALUES (?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
//给占位符赋值
ps.setString(1,ename);
ps.setString(2,job);
ps.setString(3,mgr);
ps.setString(4,hiredate);
ps.setString(5,sal);
ps.setString(6,comm);
ps.setString(7,deptno);
int i = ps.executeUpdate();
if (i>0) System.out.println("添加成功!");
else System.out.println("添加失败!");
} catch (Exception e) {
e.printStackTrace();
}
}
//删除数据
@Test
public void deleteEmp(){
Scanner input = new Scanner(System.in);
System.out.print("请输入要删除的员工编号:");
String empno = input.next();
try {
String sql = " delete from employee where empno = ? ";
ps = conn.prepareStatement(sql);
ps.setString(1,empno);
int i = ps.executeUpdate();
if (i>0) System.out.println("删除成功!");
else System.out.println("删除失败!");
} catch (SQLException e) {
e.printStackTrace();
}
}
//修改数据
@Test
public void updateEmp(){
Scanner input = new Scanner(System.in);
System.out.print("请输入要修改的员工编号:");
String empno = input.next();
System.out.print("请输入修改后的姓名:");
String ename = input.next();
System.out.print("请输入修改后的奖金:");
String comm = input.next();
try {
String sql = " update employee set ename= ? ,comm = ? where empno = ? ";
ps = conn.prepareStatement(sql);
ps.setString(1,ename);
ps.setString(2,comm);
ps.setString(3,empno);
int i = ps.executeUpdate();
if (i>0) System.out.println("修改成功!");
else System.out.println("修改失败!");
} catch (SQLException e) {
e.printStackTrace();
}
}
//查询数据
@Test
public void queryEmp(){
Scanner input = new Scanner(System.in);
System.out.print("请输入要查询的员工编号:");
String no = input.next();
try {
String sql = "select * from employee where empno = ?";
ps = conn.prepareStatement(sql);
ps.setString(1,no);
ResultSet rs = ps.executeQuery();
while (rs.next()){
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
Integer mgr = rs.getInt("mgr");
java.sql.Date hiredate = rs.getDate("hiredate");
double sal = rs.getDouble("sal");
double comm = rs.getDouble("comm");
int deptno = rs.getInt("deptno");
System.out.println(empno + "\t"+ename+"\t"+job+"\t"+mgr+"\t"+hiredate+"\t"+sal+"\t"+comm+"\t"+deptno);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
ResultSet与ResultSetMetaData
ResultSet
查询需要调用PreparedStatement 的 executeQuery() 方法,查询结果是一个ResultSet 对象
ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集,ResultSet 接口由数据库厂商提供实现
ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面。
ResultSet 对象维护了一个指向当前数据行的游标,初始的时候,游标在第一行之前,可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效,该方法返回 true,且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体。
当指针指向一行时, 可以通过调用 getXxx(int index)
或 getXxx(int columnName)
获取每一列的值。
-
例如:getInt(1), getString(“name”)
-
注意:Java与数据库交互涉及到的相关Java API中的索引都从1开始。
ResultSet 接口的常用方法:
-
boolean next()
-
getString()
ResultSetMetaData
可用于获取关于 ResultSet 对象中列的类型和属性信息的对象。
ResultSetMetaData meta = rs.getMetaData();
getColumnName(int column):获取指定列的名称
getColumnLabel(int column):获取指定列的别名
getColumnCount():返回当前 ResultSet 对象中的列数。
getColumnTypeName(int column):检索指定列的数据库特定的类型名称。
getColumnDisplaySize(int column):指示指定列的最大标准宽度,以字符为单位。
isNullable(int column):指示指定列中的值是否可以为 null。
isAutoIncrement(int column):指示是否自动为指定列进行编号,这样这些列仍然是只读的。
关于ResultSetMetaData
1、如何获取 ResultSetMetaData: 调用 ResultSet 的 getMetaData() 方法即可。
2、获取 ResultSet 中有多少列:调用 ResultSetMetaData 的 getColumnCount() 方法。
3、获取 ResultSet 每一列的列的别名是什么:调用 ResultSetMetaData 的getColumnLabel() 方法。
资源的释放
释放ResultSet,Statement,Connection。
数据库连接(Connection)是非常稀有的资源,用完后必须马上释放,如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建,尽量早的释放。
可以在finally中关闭,保证及时其他代码出现异常,资源也一定能被关闭。
在BaseDao中封装数据库操作
DAO:Data Access Object访问数据信息的类和接口,包括了对数据的CRUD(Create、Retrival、Update、Delete),而不包含任何业务相关的信息。有时也称作:BaseDAO。
作用:为了实现功能的模块化,更有利于代码的维护和升级。
package com.coydone.dao;
import java.sql.*;
//封装jdbc
public class BaseDao {
//连接数据库参数
private static String driverClass = "com.mysql.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/kgc7_22";
private String username = "root";
private String password = "root123";
//静态代码块获取驱动
static {
try {
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取数据库连接方法
public Connection getconn(){
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关闭数据库连接
public void close(Connection conn, Statement stat, ResultSet rs){
try {
if (rs != null) rs.close();
if (stat != null) stat.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//增删改操作封装
public Integer executeUpdate(String sql, Object[] obj){
//获取数据库连接对象
Connection conn = this.getconn();
try {
//预编译sql
PreparedStatement ps = conn.prepareStatement(sql);
//占位符
for (int i = 0; i < obj.length ; i++) {
ps.setObject(i+1,obj[i]);
}
int i = ps.executeUpdate();
//关闭连接
this.close(conn,ps,null);
return i;
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
//查询封装,有返回的结果集,传入的参数为sql语句,和可变参数
public ResultSet executeQuery(String sql,Object... obj) throws SQLException {
Connection conn = this.getconn();
PreparedStatement ps = conn.prepareStatement(sql);
//占位符
for (int i = 0; i < obj.length; i++) {
ps.setObject(i+1,obj[i]);
}
ResultSet rs = ps.executeQuery();
//关闭资源
//this.close(conn,ps,rs);
return rs;
}
}
使用反射、配置文件形式封装jdbc操作
package com.coydone.dao;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
//使用反射 配置文件形式封装jdbc操作
public class Base2Dao<T> {
//获取配置文件内容
//封装数据
private static String driverClass;
private static String url;
private static String username;
private static String password;
//读取配置文件
static {
try {
//加载并读取文件
//p.load(new FileReader("database.properties"));
//获取配置文件内容
InputStream is = Base2Dao.class.getClassLoader().getResourceAsStream("database.properties");
//创建对象
Properties p = new Properties();
//把流中的数据加载到Properties对象中
p.load(is);
driverClass = p.getProperty("driverClass");
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
//加载驱动
static {
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//封装获取数据库连接对象
public Connection getConn() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
//进行抛出异常处理
throw new RuntimeException(e);
}
}
//关闭连接,释放资源
public void close(Connection conn, Statement stat, ResultSet rs) {
try {
if (rs != null) rs.close();
if (stat != null) stat.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//增删改操作
public Integer executeUpdate(String sql, Object[] obj) throws Exception{
//获取数据库连接对象
Connection conn = this.getConn();
PreparedStatement ps = conn.prepareStatement(sql);
//占位符
for (int i = 0; i < obj.length ; i++) {
ps.setObject(i+1,obj[i]);
}
int i = ps.executeUpdate();
//关闭连接
this.close(conn,ps,null);
return i;
}
//查询操作
public List<T> executeQuery(String sql,Class c,Object... obj) throws Exception {
//存放结果集
List<T> list = new ArrayList<>();
//获取连接
Connection conn = this.getConn();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
ps.setObject(i+1,obj[i]);
}
ResultSet rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData(); //获取当前结果集的元数据
int length = metaData.getColumnCount(); //可以获取当前结果集中字段总数
while (rs.next()){
//创建bean,映射结果集
T bean = (T)c.newInstance();
for (int i = 0 ; i < length ; i++){
//获取指定列的字段名称
String columnName = metaData.getColumnName(i + 1);
//通过当前列名称获取获取当前行对应的值
Object columnValue = rs.getObject(columnName);
//通过反射把获取到的值映射到JavaBean中
//获取属性对象
Field field = c.getDeclaredField(columnName);
//开权限
field.setAccessible(true);
//把值赋值给对应的bean对象
field.set(bean,columnValue);
}
//把bean中的值存储到list集合中去
list.add(bean);
}
this.close(conn,ps,rs);
return list;
}
}
database.properties
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/kgc7_22
username = root
password = root123
JDBC API小结
两种思想
-
面向接口编程的思想。
-
ORM思想(object relational mapping)对象关系映射。
-
一个数据表对应一个java类
-
表中的一条记录对应java类的一个对象
-
表中的一个字段对应java类的一个属性
-
两种技术
-
JDBC结果集的元数据:ResultSetMetaData
-
获取列数:getColumnCount()
-
获取列的别名:getColumnLabel()
-
-
通过反射,创建指定类的对象,获取指定的属性并赋值。
评论区