java jdbc 增删改封装_java-关于JDBC操作CDUR——1--增删改封装

前言：最先我开始讲讲Statement接口吧

首先他很垃圾，毕竟原始。他有如下缺点：

1.需要拼写sql语句 ，2.并且存在SQL注入的问题

怎么解决了SQL注入问题呢：

只要用 PreparedStatement(从Statement扩展而来)

他是他的子接口

注意：关闭资源，先开后关，先关后开

1、PreparedStatement插入案例：

/**

* @Author: kangjk

* @CreateDate: 2020/6/1 16:55

*/

public class PreparedStatementUpdateTest1 {

@Test

public void testInsert()

{

Connection conn=null;

PreparedStatement ps = null;

try {

// 1.读取配置文件中的4个基本信息

InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

Properties pros = new Properties();

pros.load(is);

String user = pros.getProperty("user");

String password = pros.getProperty("password");

String url = pros.getProperty("url");

String driverClass = pros.getProperty("driverClass");

// 2.加载驱动

Class.forName(driverClass);

// 3.获取连接

conn = DriverManager.getConnection(url, user, password);

// 4.预编译SQL语句

String sql = "insert into customers(name,email,birth) value(?,?,?)";//? 占位符

ps = conn.prepareStatement (sql);

// 5.填充占位符

ps.setString (1,"雷迪嘎嘎");

ps.setString (2,"leidiGAGA@163.com");

SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd");

java.util.Date date= sdf.parse ("877-1-1");//Dete 为util下的,记得抛异常！

//为时间数据，得处理

ps.setDate (3,new Date(date.getTime ()));

// 6.执行操作

ps.execute ();

} catch (IOException e) {

e.printStackTrace ( );

} catch (ClassNotFoundException e) {

e.printStackTrace ( );

} catch (SQLException e) {

e.printStackTrace ( );

} catch (ParseException e) {

e.printStackTrace ( );

} finally {

// 7.资源关闭

if (conn!=null){

try {

conn.close ();

} catch (SQLException e) {

e.printStackTrace ( );

}

}

if (ps!=null)

{

try {

ps.close ();

} catch (SQLException e) {

e.printStackTrace ( );

}

}

}

}

}

2、咋看一大坨很不爽，于是开始封装

package com.jdbc.cc1.statement.crud;

import java.io.InputStream;

import java.sql.*;

import java.util.Properties;

public class JDBCUtils {

/**

* @Description: 获取数据库连接

* @Author: Jk_kang

* @CreateDate: 2020/6/1 17:18

*/

public static Connection getConnection() throws Exception {

// 1.读取配置文件中的4个基本信息

InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

Properties pros = new Properties();

pros.load(is);

String user = pros.getProperty("user");

String password = pros.getProperty("password");

String url = pros.getProperty("url");

String driverClass = pros.getProperty("driverClass");

// 2.加载驱动

Class.forName(driverClass);

// 3.获取连接

Connection conn = DriverManager.getConnection(url, user, password);

return conn;

}

/**

* @Description: 关闭连接和Statement的操作

* @Author: Jk_kang

* @CreateDate: 2020/6/1 19:50

*/

public static void closeResource(Connection conn, Statement ps){

try {

if(ps != null)

ps.close();

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(conn != null)

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

/**

* @Description: 关闭连接和Statement的操作

* @Author: Jk_kang

* @CreateDate: 2020/6/1 19:51

*/

public static void closeResource(Connection conn, Statement ps, ResultSet rs){

try {

if(ps != null)

ps.close();

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(conn != null)

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

try {

if(rs != null)

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

3、封装了这个现在写个修改语句测试下呗

package com.jdbc.cc2.perparedstatement.crud;

import java.sql.Connection;

import java.sql.PreparedStatement;

public class PreparedStatementUpdateTest2 {

public static void main(String[] args) {

/**

* @Description: 修改操作测试工具类

* @Author: Jk_kang

* @CreateDate: 2020/6/1 20:31

*/

Connection conn = null;

PreparedStatement ps = null;

try {

//1、获取数据库连接

conn = JDBCUtils2.getConnction ( );

//2、预编译SQL语句，返回PreparedStatement的实例

String sql = "update customers set name = ? where id = ?";

ps = conn.prepareStatement (sql);

//3、填充占位符

ps.setObject (1,"雷迪嘎嘎！！!");

ps.setObject (2,19);

//4、执行操作

ps.execute ();

} catch (Exception e) {

e.printStackTrace ( );

} finally {

//5、资源关闭

JDBCUtils2.closeConnection (conn,ps);

}

}

}

从上一个封装的工具类来看

步骤确实少了(连接和关闭省了)，现在似乎发现步骤2，3也可以进行封装呢(发现占位符，一会儿一个，一会儿两个，甚至可以没有)。虽说CRUD不同，但是也可以

封装成一个通用的增删改(加料：可变形参)

package com.jdbc.cc2.perparedstatement.crud;

import org.junit.Test;

import java.sql.Connection;

import java.sql.PreparedStatement;

public class PreparedStatementUpdateTest3 {

public static void main(String[] args) {

//注意：@Test不要带参数，比如说public void all(String path){}就要改成public void all(){}

//所有这次公交车测试用 main

PreparedStatementUpdateTest3 psut = new PreparedStatementUpdateTest3 ( );

//添加

String sql = "insert into `user`(id,name) values (?,?)";

psut.bus (sql,6,"高富帅");

}

/**

* @Description: 通用的增删改

* @Author: Jk_kang

* @CreateDate: 2020/6/1 20:45

*/

@Test//sql中占位符个数应与可变形参长度一致

public void bus(String sql,Object ...args)//bus应该是通用的意思吧，英语不太懂

{

Connection conn = null;

PreparedStatement ps = null;

try {

//1、获取数据库连接

conn = JDBCUtils2.getConnction ( );

//2、预编译sql语句，返回PreparedStatement的实例

ps = conn.prepareStatement (sql);

//3、填充占位符

for (int i = 0; i < args.length; i++) {

ps.setObject (i+1,args[i]);//参数1：占位符从1开始，args数组取值从下标0开始

}

//4、执行

ps.execute ();

} catch (Exception e) {

e.printStackTrace ( );

} finally {

//5、资源关闭

JDBCUtils2.closeConnection (conn,ps);

}

}

}