JDBC
1、简介
JDBC:Java Database Connectivity,它是代表一组独立于任何数据库管理系统(DBMS)的API,声明在java.sql与javax.sql包中,是SUN(现在Oracle)提供的一组接口规范。由各个数据库厂商来提供实现类,这些实现类的集合构成了数据库驱动jar。
2、JDBC使用步骤
(1)注册驱动
(2)获取数据库连接对象Connection
(3)创建SQL语句
(4)执行SQL语句,并且返回结果集
(5)处理结果集
(6)释放连接
实例代码1:增、删、改
public class TestConnection {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//mysql的url格式:jdbc协议:子协议://主机名:端口号/要连接的数据库名
String url = "jdbc:mysql://localhost:3306/mytest";
String user = "root";
String password = "123";
//获取连接
Connection conn = DriverManager.getConnection(url,user,password);
//编写sql
String sql = "insert into student values(null,'张三','北京')";
Statement statement = conn.createStatement();
//执行sql,statement.executeUpdate(sql)可以执行增删改的sql
int len = statement.executeUpdate(sql);
//处理结果
System.out.println(len>0?"成功":"失败");
//关闭
statement.close();
conn.close();
}
}
示例代码2:查询
public class TestSelect {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mytest", "root", "123");
//创建sql
String sql = "select * from student";
//获取statement对象
Statement statement = connection.createStatement();
//执行sql
ResultSet resultSet = statement.executeQuery(sql);
//处理结果
while(resultSet.next()) {
Object id = resultSet.getObject(1);
Object name = resultSet.getObject(2);
Object address = resultSet.getObject(3);
System.out.println(id +"\t" + name + "\t"+ address);
}
resultSet.close();
statement.close();
connection.close();
}
}
3、使用PreparedStatement实现CRUD
3.1、通过PreparedStatement来解决Statement的问题
3.1.1、Statement的问题
(1)sql拼接
String sql = "insert into t_employee(ename,tel,gender,salary) values('" + ename + "','" + tel + "','" + gender + "'," + salary +")";
Statement st = conn.createStatement();
int len = st.executeUpdate(sql);
(2)sql注入
String sql = "SELECT * FROM t_employee where ename='" + ename + "'";
//如果我此时从键盘输入ename值的时候,输入:张三' or '1'= '1
//结果会把所有数据都查询出来
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
(3)处理blob等类型的数据
String sql = "insert into user(username,photo) values('chailinyan', 图片字节流)";
//此时photo是blob类型的数据时,无法在sql中直接拼接
3.1.2、PreparedStatement解决问题
(1)避免sql拼接
String sql = "insert into t_employee(ename,tel,gender,salary) values(?,?,?,?)";
PreparedStatement pst = conn.prepareStatement(sql);//这里要传带?的sql,然后mysql端就会对这个sql进行预编译
//设置?的具体值
/*pst.setString(1, ename);
pst.setString(2, tel);
pst.setString(3, gender);
pst.setDouble(4, salary);*/
pst.setObject(1, ename);
pst.setObject(2, tel);
pst.setObject(3, gender);
pst.setObject(4, salary);
int len = pst.executeUpdate();//此处不能传sql
System.out.println(len);
(2)不会有sql注入
String sql = "SELECT * FROM t_employee where ename=?";
//即使输入'张三' or '1'= '1'也没问题
PreparedStatement pst = conn.prepareStatement(sql);
//中间加入设置?的值
pst.setObject(1, ename);
ResultSet rs = pst.executeQuery();
(3)处理blob类型的数据
String sql = "insert into user(username,photo) values(?,?)";
PreparedStatement pst = conn.prepareStatement(sql);
//设置?的值
pst.setObject(1, "chailinyan");
FileInputStream fis = new FileInputStream("D:/QMDownload/img/美女/15.jpg");
pst.setBlob(2, fis);
int len = pst.executeUpdate();
System.out.println(len>0?"成功":"失败");
-
注意两个问题:
①my.ini关于上传的字节流文件有大小限制,可以在my.ini中配置变量
max_allowed_packet=16M
②每一种blob有各自大小限制:
tinyblob:255字节、blob:65k、mediumblob:16M、longblob:4G
3.1.3、获取自增长键值
/*
* 我们通过JDBC往数据库的表格中添加一条记录,其中有一个字段是自增的,那么在JDBC这边怎么在添加之后直接获取到这个自增的值
* PreparedStatement是Statement的子接口。
* Statement接口中有一些常量值:
* (1)Statement.RETURN_GENERATED_KEYS
*
* 要先添加后获取到自增的key值:
* (1)PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
* (2)添加sql执行完成后,通过PreparedStatement的对象调用getGeneratedKeys()方法来获取自增长键值,遍历结果集
* ResultSet rs = pst.getGeneratedKeys();
*/
public class TestAutoIncrement {
public static void main(String[] args) throws Exception{
//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2、获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mytest", "root", "123");
//3、执行sql
String sql = "insert into t_department values(null,?,?)";
/*
* 这里在创建PreparedStatement对象时,传入第二个参数的作用,就是告知服务器端
* 当执行完sql后,把自增的key值返回来。
*/
PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//设置?的值
pst.setObject(1, "测试部");
pst.setObject(2, "测试项目数据");
//执行sql
int len = pst.executeUpdate();//返回影响的记录数
if(len>0){
//从pst中获取到服务器端返回的键值
ResultSet rs = pst.getGeneratedKeys();
//因为这里的key值可能多个,因为insert语句可以同时添加多行,所以用ResultSet封装
//这里因为只添加一条,所以用if判断
if(rs.next()){
Object key = rs.getObject(1);
System.out.println("自增的key值did =" + key);
}
}
//4、关闭
pst.close();
conn.close();
}
}
3.1.4、事务
/*
* mysql默认每一个连接是自动提交事务的。
* 那么当我们在JDBC这段,如果有多条语句想要组成一个事务一起执行的话,那么在JDBC这边怎么设置手动提交事务呢?
* (1)在执行之前,设置手动提交事务
* Connection的对象.setAutoCommit(false)
* (2)成功:
* Connection的对象.commit();
* 失败:
* Connection的对象.rollback();
*
* 补充说明:
* 为了大家养成要的习惯,在关闭Connection的对象之前,把连接对象设置回自动提交
* (3)Connection的对象.setAutoCommit(true)
*
* 因为我们现在的连接是建立新的连接,那么如果没有还原为自动提交,没有影响。
* 但是我们后面实际开发中,每次获取的连接,不一定是新的连接,而是从连接池中获取的旧的连接,而且你关闭也不是真关闭,
* 而是还给连接池,供别人接着用。以防别人拿到后,以为是自动提交的,而没有commit,最终数据没有成功。
*/
public class TestTransaction {
public static void main(String[] args) throws Exception{
/*
* 一般涉及到事务处理的话,那么业务逻辑都会比较复杂。
* 例如:购物车结算时:
* (1)在订单表中添加一条记录
* (2)在订单明细表中添加多条订单明细的记录(表示该订单买了什么东西)
* (3)修改商品表的销量和库存量
* ...
* 那么我们今天为了大家关注事务的操作,而不会因为复杂的业务逻辑的影响导致我们的理解,那么我们这里故意
* 用两条修改语句来模拟组成一个简单的事务。
* update t_department set description = 'xx' where did = 2;
* update t_department set description = 'yy' where did = 3;
*
* 我希望这两天语句要么一起成功,要么一起回滚
* 为了制造失败,我故意把第二条语句写错
* update t_department set description = 'yy' (少了where) did = 3;
*/
//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2、获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
//设置手动提交事务
conn.setAutoCommit(false);
//3、执行sql
String sql1 = "update t_department set description = 'xx' where did = 2";
String sql2 = "update t_department set description = 'yy' did = 3";//这是错的
//使用prepareStatement的sql也可以不带?
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql1);
int len = pst.executeUpdate();
System.out.println("第一条:" + (len>0?"成功":"失败"));
pst = conn.prepareStatement(sql2);
len = pst.executeUpdate();
System.out.println("第二条:" + (len>0?"成功":"失败"));
//都成功了,就提交事务
System.out.println("提交");
conn.commit();
} catch (Exception e) {
System.out.println("回滚");
//失败要回滚
conn.rollback();
}
//4、关闭
pst.close();
conn.setAutoCommit(true);//还原为自动提交
conn.close();
}
}