一.完成Druid连接池工具类
- 简单来说就是运用
JDBCUtils.class.getClassLoader().getResourceAsStream(“druid.properties”)加载配置文件。 - 之后运用
ds = DruidDataSourceFactory.createDataSource(pro)获取DataSource
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtils {
private static DataSource ds;
static {
try {
Properties pro =new Properties();
//1.加载配置文件
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
/**
* JDBCUtils.class 获取当前对象所处的Class类
* getClassLoader() 取得该Class对象的类装载器
* getResourceAsStream() 调用类装载器得方法加载资源
*/
//2.获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取资源
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//释放资源
public static void close( Statement stat, Connection conn){
// if (stat!=null){
// try {
// stat.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
// if (conn!=null){
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
close(null,stat,conn);
}
public static void close(ResultSet res,Statement stat,Connection conn){
if (res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat!=null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//获取连接池的方法
public static DataSource getDataSource(){
return ds;
}
}
二:JDBCTemplate测试
import cn.itcast.datasource.uilts.JDBCUtils;
import cn.itcast.domain.Emp;
import com.sun.org.apache.xalan.internal.xsltc.compiler.Template;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.JDBCType;
import java.util.List;
import java.util.Map;
public class JdbcTemplateDemo1 {
/**
* 调用JdbcTemplate
* 1.update():执行DML语句。增删改语句
* 2.queryForMap():查询结果将结果集封装为map集合
* 3.queryForList():查询结果将结果集封装为list集合
* 4.query()查询结果,将结果封装为JavaBen对象
* →new BeanPropertyRowMapper<Emp>(Emp.class)
* 5,queryForObject:查询结果,将结果封装为对象
*/
//改
@Test
public void test1(){
//1.获取JDBCTemplate对象
JdbcTemplate template =new JdbcTemplate(JDBCUtils.getDataSource());
//2.定义sql
String sql = "Update test0 set many = 5000 where id =1";
//3.执行SQL语句
int update = template.update(sql);
System.out.println(update);
}
//增
@Test
public void test2(){
//1.获取JDBCTemplate得对象
JdbcTemplate template =new JdbcTemplate(JDBCUtils.getDataSource());
//2.定义sql语句
String sql = "insert into test0 values(null,?,?)";
//3.执行sql语句
/**
* pstat = conn.prepareStatement(sql);
* stat.setString(1,"赵六");
* pstat.setInt(2,300);
*/
int update = template.update(sql, "赵四", 1000);
System.out.println(update);
}
//删
@Test
public void test3(){
//1.创建JDBCTemplate对象
JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
//2.定义sql语句
String sql ="delete from test0 where id=?";
int update = template.update(sql,5);
System.out.println(update);
}
//查询id=1得数据,将其封装为Map集合
@Test
public void test4(){
JdbcTemplate template =new JdbcTemplate(JDBCUtils.getDataSource());
String sql ="select *from test0 where id=?";
Map<String, Object> map = template.queryForMap(sql, 1);
System.out.println(map);
//{id=1, name=李帅, many=5000}
}
//查询所有记录,将其封装为List
@Test
public void test5(){
JdbcTemplate template =new JdbcTemplate(JDBCUtils.getDataSource());
String sql ="select *from test0";
List<Map<String, Object>> maps = template.queryForList(sql);
for (Map<String, Object> map : maps) {
System.out.println(map);
}
}
//查询所有记录,将其封装为Emp对象得List集合
@Test
public void test6(){
JdbcTemplate template =new JdbcTemplate(JDBCUtils.getDataSource());
String sql = "select *from test0";
List<Emp> query = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : query) {
System.out.println(emp);
}
}
//查询总记录数
@Test
public void tset7(){
JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
String sql="select count(id) from test0";
Integer integer = template.queryForObject(sql, Integer.class);
System.out.println(integer);
}
}