标题:JDBC:使用德鲁伊连接池封装增删改 、查
一、封装增删改
- 首先导入jar架包
- 重写配置文件druid.properties
1.1步骤:
1)建立连接
Connection connection=JDBCUtilsByDruid.getConnection();
2)创建执行sql语句的对象statement,执行sql语句【执行之前还需为占位符?赋值】
PreparedStatement statement=connection.prepareStatement(sql);
//设置占位符的值
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);
}
int count=statement.executeUpdate();
3)执行完后,获得返回值,关闭connection对象,执行sql语句的对象statement
TestUtils02.getClose(null, statement, connection);
封装增删改的代码如下:
//封装增删改
public static int update(String sql,Object...params) throws Exception {
//建立连接
Connection connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
PreparedStatement statement=connection.prepareStatement(sql);
//设置占位符的值
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);
}
int count=statement.executeUpdate();
//3.关闭
TestUtils02.getClose(null, statement, connection);
return count;
}
二、封装查
1.1查询一条记录
步骤:
1)建立连接connection=JDBCUtilsByDruid.getConnection();
2)执行sql语句
- 创建执行sql语句的对象statement,执行sql语句【执行之前还需为占位符?赋值】
- 还需实现创建Boys类,与Boys表对应,【类和表的所有列对应,对象和表的每一行对应】
- 创建了Boys的对象,用来存储表的一行数据,作为返回值【赋值后】
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();//返回一个对象,为一个对象赋值,故需要得到对象的class
Boys boys=null;
if(set.next()) {
boys=new Boys(set.getInt("id"),set.getString("boyName"),set.getString("userCP"));
}
3)关闭JDBCUtilsByDruid.getClose(set, statement, connection);
代码如下【查一个记录 只针对boys表】:
/**
* 封装查
* 查一个记录 只针对boys表
* @throws Exception
*/
public static Boys querySingle(String sql,Object... params){
Connection connection= null;
ResultSet set=null;
PreparedStatement statement=null;
try {
//1.建立连接
connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();//返回一个对象,为一个对象赋值,故需要得到对象的class
Boys boys=null;
if(set.next()) {
boys=new Boys(set.getInt("id"),set.getString("boyName"),set.getString("userCP"));
}
return boys;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
//3.关闭
JDBCUtilsByDruid.getClose(set, statement, connection);
}
}
1.2查询多条记录
1)建立连接connection=JDBCUtilsByDruid.getConnection();
2)执行sql语句
- 创建执行sql语句的对象statement,执行sql语句【执行之前还需为占位符?赋值】
- 还需实现创建Boys类,与Boys表对应,【
- 创建了List的对象,用来存储表中多行数据,list作为返回值【赋值后】
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();//返回一个list,为一个list赋值,仍然需要得到对象的class
List<Boys> list=new ArrayList<>();
while(set.next()) {
Boys boys=new Boys(set.getInt("id"),set.getString("boyName"),set.getString("userCP"));
list.add(boys);
}
3)关闭,同上
代码如下【查多个记录,只针对boys表】:
/**
* 封装查
* 查多个记录,只针对boys表
* @throws Exception
*/
public static List<Boys> queryMulti(String sql,Object... params) {
Connection connection= null;
ResultSet set=null;
PreparedStatement statement=null;
try {
//1.建立连接
connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();//返回一个list,为一个list赋值,仍然需要得到对象的class
List<Boys> list=new ArrayList<>();
while(set.next()) {
Boys boys=new Boys(set.getInt("id"),set.getString("boyName"),set.getString("userCP"));
list.add(boys);
}
return list;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
//3.关闭
JDBCUtilsByDruid.getClose(set, statement, connection);
}
}
1.3查询一个记录,
1)建立连接,同上
2)执行sql语句
- 创建执行sql语句的对象statement,执行sql语句【执行之前还需为占位符?赋值】
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();
Object obj=null;
if(set.next()) {
obj=set.getObject(1);
}
- 返回
3)关闭
代码如下【查单个记录】:
/**
* 封装查
* 查单个记录
* @throws Exception
*/
public static Object queryObject(String sql,Object... params) {
Connection connection= null;
ResultSet set=null;
PreparedStatement statement=null;
try {
//1.建立连接
connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();
Object obj=null;
if(set.next()) {
obj=set.getObject(1);
}
return obj;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
//3.关闭
JDBCUtilsByDruid.getClose(set, statement, connection);
}
}
完整代码如下:
public class CRUDUtils02 {
//封装增删改
public static int update(String sql,Object...params) throws Exception {
//建立连接
Connection connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
PreparedStatement statement=connection.prepareStatement(sql);
//设置占位符的值
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);
}
int count=statement.executeUpdate();
//3.关闭
TestUtils02.getClose(null, statement, connection);
return count;
}
/**
* 封装查
* 查一个记录 只针对boys表
* @throws Exception
*/
public static Boys querySingle(String sql,Object... params){
Connection connection= null;
ResultSet set=null;
PreparedStatement statement=null;
try {
//1.建立连接
connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();//返回一个对象,为一个对象赋值,故需要得到对象的class
Boys boys=null;
if(set.next()) {
boys=new Boys(set.getInt("id"),set.getString("boyName"),set.getString("userCP"));
}
return boys;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
//3.关闭
JDBCUtilsByDruid.getClose(set, statement, connection);
}
}
/**
* 封装查
* 查多个记录,只针对boys表
* @throws Exception
*/
public static List<Boys> queryMulti(String sql,Object... params) {
Connection connection= null;
ResultSet set=null;
PreparedStatement statement=null;
try {
//1.建立连接
connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();//返回一个list,为一个list赋值,仍然需要得到对象的class
List<Boys> list=new ArrayList<>();
while(set.next()) {
Boys boys=new Boys(set.getInt("id"),set.getString("boyName"),set.getString("userCP"));
list.add(boys);
}
return list;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
//3.关闭
JDBCUtilsByDruid.getClose(set, statement, connection);
}
}
/**
* 封装查
* 查单个记录
* @throws Exception
*/
public static Object queryObject(String sql,Object... params) {
Connection connection= null;
ResultSet set=null;
PreparedStatement statement=null;
try {
//1.建立连接
connection=JDBCUtilsByDruid.getConnection();
//2.执行sql语句
statement=connection.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
statement.setObject(i+1, params[i]);//细心
}
set=statement.executeQuery();
Object obj=null;
if(set.next()) {
obj=set.getObject(1);
}
return obj;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
//3.关闭
JDBCUtilsByDruid.getClose(set, statement, connection);
}
}
}