JDBC连接操作数据库
1.1 Driver:
Java.sql.Driver 接口是所有 JDBC 驱动程序需要实现的接口。这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现
在程序中不需要直接去访问实现了 Driver 接口的类，而是由驱动程序管理器类(java.sql.DriverManager)去调用这些Driver实现
操作步骤：
1.2 四种链接方式
方式一：以下生成的Driver是固定mysql的，方式二：可以使用反射根据传入的动态生成各种数据库驱动
@Test
public void test1() throws SQLException {
// JDBC的连接测试
// 1. driver // 每个数据库驱动程序需要对Driver接口进行实现
Driver driver = new com.mysql.jdbc.Driver();
// 2. url: jdbc:mysql://127.0.0.1:3306/mybatis
String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=true";
// 3. 封装属性
Properties pro = new Properties();
pro.setProperty("user","root");
pro.setProperty("password","password");
// driver获取数据库的连接
Connection connect = driver.connect(url, pro);
System.out.println(connect);
connection.close();
}
DriverManager 静态
方式三：利用DriverManager替换Driver
@Test
public void test2()throws Exception{
// 使用DriverManager替换driver
Class<?> cla = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) cla.newInstance();
String url = "jdbc:mysql://localhost:3306/mybatis?useSSL=true";
String user = "root";
String password = "password";
// 注册Driver
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
}
方式四： // 在方式三的基础上，多部分代码默认已经完成了
@Test
public void test2()throws Exception{
// 使用DriverManager替换driver
/*Class<?> cla = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) cla.newInstance();*/
String url = "jdbc:mysql://localhost:3306/mybatis?useSSL=true";
String user = "root";
String password = "password";
// 注册Driver
//DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
}
1. 连接数据库最终版
方式五： 配置信息放在配置文件中，代码读取
@Test
public void test3()throws Exception{
// 读取配置文件，加载配置信息
Properties pro = new Properties();
// 获取配置文件流,FileInputStream默认在当前项目下， 类加载器默认在src下或者resource资源目录下
//FileInputStream fis = new FileInputStream("src\\properties.properties"); 不行，找不到resource目录下的资源
// 类加载器加载
ClassLoader loader = JdbcTest.class.getClassLoader();
InputStream fis = loader.getResourceAsStream("properties.properties");
pro.load(fis);
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
String driv = pro.getProperty("driver");
Class<?> cla = Class.forName(driv);
Driver driver = (Driver) cla.newInstance();
// 注册Driver
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
connection.close();
fis.close();
}
1.4 Statement
弊端：SQL注入问题：由于Statement的sql语句是拼接在，在输入数据时，输入数据有可能拼接成sql的关键字，导致出现sql语句执行出现异常
如何避免SQL注入问题：使用PreparedStatement替换Statement
@Test
public void test4() throws Exception {
// statement 执行sql语句
// 自定义的JDBC工具类获取数据库连接
Connection connection = JDBCUtils.getConnection();
System.out.println(connection);
// 4.创建sql语句的执行对象Statement
Statement statement = connection.createStatement();
// 5.用statement对象执行sql语句，可能有返回的结果集（链表的形式）
// 执行查询语句用executeQuery()，执行插入、删除、修改用executeUpdate()
// execute(),增删改查的sql都能执行，有判断效率会低点
ResultSet resultSet = statement.executeQuery("select *from mybatis.books");
while (resultSet.next()){
System.out.print(resultSet.getInt("bookID")+"\t"); //在不知道类型的情况下可以用getObject()获取
System.out.print(resultSet.getObject("bookName")+"\t");// 里面的参数要和数据库一一对应
System.out.println(resultSet.getObject("detail")+"\t");
}
// 6.释放连接，关闭资源
resultSet.close();
statement.close();
connection.close();
}
1.5 PreparedStatement
因为sql语句是预编译的，而且语句中使用了占位符，规定了sql语句的结构。用户可以设置"?"的值，但是不能改变sql语句的结构，因此想在sql语句后面加上如“or 1=1”实现sql注入是行不通的。
connection.prepareStatement(sql); // 预编译，得到后结构就固定了
优点：
-
可以实更高效的批量操作
-
sql语句在预编译后就会缓存下来，继续执行同样的sql语句时，就不会有sql语句的校验
for(int i=0;i < 100;i++){ pre.setObject(""); pre.execute(); } -
以上优化：sql只有值在变化，在多存储一些值，后在提交执行插入操作
- addBatch( )、executeBatch( )、clearBatch( )
- mysq1服务器默认是关闭批处理的，我们需要通过一个参数， 让mysq1开启批处理的支持。? rewriteBatchedstatements=true写在配置文件的ur1后面
- 3.使用更新的mysql 驱动: mysql-connector-java-5.1.37-bin.jar
for(int i=0;i < 100;i++){ pre.setObject(""); // 1.攒sql pre.addBatch(); if(i <= 10){ // 执行bathch pre.executeBatch(); //清空batch pre.clearBatch(); } }
-
插入：
@Test
public void test5() throws SQLException {
// 使用PreparedStatement避免Statement的sql注入问题
// 使用了占位符，解决sql注入问题
// 自定义的JDBC工具类获取数据库连接
Connection connection = JDBCUtils.getConnection();
String sql = "INSERT into mybatis.books values(?,?,?,?)";
PreparedStatement pre = connection.prepareStatement(sql);
pre.setInt(1,31);
pre.setString(2,"java神书");
pre.setInt(3,30);
pre.setString(4,"从入门到入狱");
pre.execute();
pre.close();
connection.close();
}
修改：
@Test
public void test6()throws Exception{
// 1. 获取数据库连接，此处是自定义的工具类
Connection connection = JDBCUtils.getConnection();
// 2. 预编译sql语句，返回PrepareStatement
String sql = "update books set bookID=? where bookID=?";
PreparedStatement pre = connection.prepareStatement(sql);
// 3. 填充占位符
pre.setInt(1,34);
pre.setInt(2,31);
// 3. 执行
pre.execute();
// 4. 资源的关闭
pre.close();
connection.close();
}
通用的执行sql的方法:利用可变形参，实现占位符的填充
public void test7(String sql,Object ...data)throws Exception{
// 1. 获取数据库连接
Connection connection = JDBCUtils.getConnection();
// 2. 预编译sql语句，返回PrepareStatement
PreparedStatement pre = connection.prepareStatement(sql);
// 3. 填充占位符
for (int i = 0; i < data.length; i++) {
pre.setObject(i+1,data[i]);
}
// 3. 执行
pre.execute();
// 4. 资源的关闭
pre.close();
connection.close();
}
查询： 遍历返回的结果集
getObject(int ) : 取出第几个返回值的结果 （从1开始）
getObject(String ) ：返回变量名是参数值得 结果
@Test
public void test9()throws Exception{
// 查询，返回结果集
// 1. 获取数据库连接
Connection connection = JDBCUtils.getConnection();
// 2. 预编译sql语句，返回PrepareStatement
PreparedStatement pre = connection.prepareStatement("select * from mybatis.books");
// 3. 执行
ResultSet resultSet = pre.executeQuery();
// 4. 遍历返回的结果
//方式一：
while (resultSet.next()){ // 结果集中的next: 判断指针的下一个位置是否有元素，返回boolean型数据，为true指针下移
System.out.print(resultSet.getInt("bookID")+"\t\t"); //在不知道类型的情况下可以用getObject(）获取
System.out.print(resultSet.getObject("bookName")+"\t\t");// 里面的参数要和数据库一一对应
System.out.println(resultSet.getObject("detail")+"\t");
}
// 5. 资源的关闭
pre.close();
connection.close();
}
获取返回的结果集中数据的列数，可以写一个通用的查询：
// 获取返回数据，有多少列
// 获取返回值的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
// 获取有多少列
int columnCount = metaData.getColumnCount();
// 获取第一列的列名
String name = metaData.getColumnName(1);
// 获取列的别名 , 没有别名就返回列名
String columnLabel = metaData.getColumnLabel(1);
1.6 别名解决字段问题
针对数据库字段名，和java实体类的属性名不一致的问题：
- sql取别名: as
查询： 结果映射，利用反射，通用查询
public <T> List<T> test1(Class<T> clazz,String sql,Object ...args)throws Exception{
/** 查询：
* 利用返回的结果集的元数据
* 获取返回的列数，列的别名，列名
* 编写一个通用(想查哪几列数据就只显示哪几列数据)的查询
* 解决：数据库字段和java实体类属性不一致问题
* */
// 1. 获取数据库连接
Connection connection = JDBCUtils.getConnection();
// 2. 获取PrepareStatement 防止Statement的sql注入问题
PreparedStatement pre = connection.prepareStatement(sql);
// 3. 填充占位符
for (int i = 0; i < args.length; i++) {
pre.setObject(i+1,args[i]);
}
// 4. 执行查询，返回结果集
ResultSet resultSet = pre.executeQuery();
// 5. 获取元数据 ， 获取列数
ResultSetMetaData metaData = resultSet.getMetaData();// 元数据
int columnCount = metaData.getColumnCount();
// 遍历结果集
List list = new ArrayList<T>();
while (resultSet.next()){
T book = clazz.newInstance();
// 因为只知道返回的属性名，对属性名的set方法不好指定，利用反射进行修改
for (int i = 0; i < columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i + 1); // 别名
Object object = resultSet.getObject(columnLabel); // 值
Field declaredField = clazz.getDeclaredField(columnLabel); //
declaredField.setAccessible(true);
declaredField.set(book,object);
}
list.add(book);
}
list.forEach(System.out::println);
pre.close();
connection.close();
if (list.size()==0) return null;
return list;
}
1.7 添加大量数据的优化
- preparedStatement.addBatch(): 缓存sql数据
- preparedStatement.executeBatch()： 执行缓存的sql
- preparedStatement.clearBatch()：清空缓存的sql
public void test5()throws Exception{
// 获取连接
Connection connection = JDBCUtils.getConnection();
// 获取sql的执行对象
String sql = "insert into books(bookID,bookName,bookCounts,detail) values(?,'java神书',11,'从入门到入狱')";
PreparedStatement pre = connection.prepareStatement(sql);
// 关闭连接的自动提交
connection.setAutoCommit(false);
// 填充sql的占位符
for (int i = 40; i <= 2000; i++) {
pre.setInt(1,i);
// 储存sql
pre.addBatch();
if ((i % 200) == 0){
// 执行sql
pre.executeBatch();
// 清空缓存的sql
pre.clearBatch();
}
}
// 提交事务 ，真正把数据插入数据库
connection.commit();
// 关闭资源
pre.close();
connection.close();
}
1.8 PreparedStatement对数据库中的Blob数据操作
Blob数据类型能够存储较大的数据
只能使用PreparedStatement进行操作，Blob不支持StatementSql语句的拼接
存图片：
把图片变成一个文件流传过去
@Test
public void test3()throws Exception{
// 获取连接
Connection connection = JDBCUtils.getConnection();
String sql = "update books set photo = ? where bookID = ?";
PreparedStatement pre = connection.prepareStatement(sql);
FileInputStream fis = new FileInputStream("E:\\初音\\test3.png");
int id = 3;
// Blob值注入，占位符
pre.setBlob(1,fis);
pre.setObject(2,id);
// 提交执行
pre.executeUpdate();
// 关闭资源
fis.close();
connection.close();
}
取图片：
getBinaryStream(String )：获取到Blob字段返回的一个流
@Test
public void test4()throws Exception{
// 获取连接
Connection connection = JDBCUtils.getConnection();
String sql = "select books.photo from books where bookID=?";
PreparedStatement pre = connection.prepareStatement(sql);
int id = 3;
// Blob值注入，占位符
pre.setObject(1,id);
// 提交执行
ResultSet resultSet = pre.executeQuery();
// 把Blob类型的字段下载下来，以文件的形式保存在本地中
InputStream photo = null;
if (resultSet.next()){
photo = resultSet.getBinaryStream("photo");
}
byte[] buffer = new byte[1024];
int len;
FileOutputStream fos = new FileOutputStream(new File("E:\\初音\\test3.png"));
while ((len = photo.read(buffer)) != -1){
fos.write(buffer,0,len);
}
// 关闭资源
if (photo == null) photo.close();
fos.close();
connection.close();
}
事务回滚：
@Test
public void test1() throws SQLException {
// 事务考虑自动提交的DML操作
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
// 取消自动提交
connection.setAutoCommit(false);
String sql1 = "delete from books where bookID =1998";
String sql2 = "delete from books where bookID =1997";
PreparedStatement pre1 = connection.prepareStatement(sql1);
// 执行
pre1.executeUpdate();
// 异常
System.out.println(10/0);
PreparedStatement pre2 = connection.prepareStatement(sql2);
pre2.executeUpdate();
// 提交
connection.commit();
if(pre1!=null) pre1.close();
if(pre2!=null) pre2.close();
}catch (Exception e){
// 事务回滚
connection.rollback();
}finally{
// 关闭资源
connection.close();
}
}
