JDBC连接池&JDBCTemplate
1、连接池
1.1概念:
存放连接的容器
1.2作用
提高性能 (节省创建和销毁连接的时间)
1.3常见的连接池
1.3.1 c3p0
- 导入jar包
2. 复制配置文件
1) 文件必须添加到src目录下
2.)文件名称不能修改
3. 创建核心类 ComboPooledDataSource
package com.itfenghua01;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;
public class DruidDemo01 {
public static void main(String[] args) throws Exception {
//获取集合对象
Properties prop = new Properties();
//读取配置文件
prop.load(DruidDemo01.class.getClassLoader().getResourceAsStream("druid.properties"));
//获取连接池
DataSource ds = DruidDataSourceFactory.createDataSource(prop);
//获取连接
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
```
## 2、JDBCTemplate ★
#### 2.1作用:
简化了JDBC操作
#### 2.2使用步骤
1. 导入jar包
2. 创建核心对象
JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
#### 2.3常见方法
1. update(String sql,Object ...) 用于执行DML语句(增删改)
2. query(String sql,new BeanPropertyRowMapper<类>(类.class),Object ...); 查询多条记录
3. queryForObject(sql, 类.class) 查询单个记录
template.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account.class))
template.queryForObject(sql, Integer.class);
```java
package com.itfenghua01;
import com.mysql.fabric.xmlrpc.base.Data;
import java.sql.Date;
public class Emp {
private Integer id;
private String name;
private String gender;
private Double salary;
private Date date;
private Integer dept_id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Integer getDept_id() {
return dept_id;
}
public void setDept_id(Integer dept_id) {
this.dept_id = dept_id;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", salary=" + salary +
", date=" + date +
", dept_id=" + dept_id +
'}';
}
}
-------------------------------------------------------
package com.itfenghua01;
import Utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class DruidTest01 {
//获取JdbcTemplate对象
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
/**
* 1. 修改1号数据的 salary 为 10000
*/
@Test
public void test01() {
String sql = "update ss set num=200 where id=?";
int update = template.update(sql, 2);
System.out.println(update);
}
/**
* 2. 添加一条记录
*/
@Test
public void Test02() {
String sql = "insert into ss values(?,?) ";
int update = template.update(sql, 1, 111111);
System.out.println(update);
}
/**
* 3. 删除刚才添加的记录
*/
@Test
public void Test03() {
String sql = "delete from ss where id=?";
int update = template.update(sql, 1);
System.out.println(update);
}
/**
* 4. 查询id为1的记录,将其封装为Map集合
* //不常用
*/
@Test
public void Test04() {
String sql = "select * from ss where id=?";
Map<String, Object> map = template.queryForMap(sql, 2);
System.out.println(map);
}
/**
* 5. 查询所有记录,将其封装为List
* //不常用
*/
@Test
public void Test05() {
String sql = "select * from ss";
List<Map<String, Object>> maps = template.queryForList(sql);
for (Map<String, Object> map : maps) {
System.out.println(map);
}
}
/**
* 6. 查询所有记录,将其封装为Emp对象的List集合
* //不常用
*/
@Test
public void Test06() {
String sql = "select * from ss";
List<Emp> empList = template.query(sql, new RowMapper<Emp>() {
@Override
public Emp mapRow(ResultSet res, int i) throws SQLException {
Emp emp = new Emp();
int id = res.getInt("id");
int num = res.getInt("num");
emp.setId(id);
//emp.setNum(num);
return emp;
}
});
for (Emp emp : empList) {
System.out.println(emp);
}
}
/**
*7. 查询总记录数
*/
@Test
public void Test07(){
String sql = "select count(*) from ss";
Integer integer = template.queryForObject(sql, Integer.class);
System.out.println(integer);
}
/**
* 8.查询一个并封装为对象
*/
@Test
public void Test08(){
JdbcTemplate tem = new JdbcTemplate(JDBCUtils.getDataSource());
String sql ="select * from ss where id=? ";
Emp emp = tem.queryForObject(sql, new BeanPropertyRowMapper<Emp>(Emp.class),4);
System.out.println(emp);
}
/**
* 9.使用提供的方法查询所有记录,将其封装为Emp对象的List集合
*/
@Test
public void Test09(){
String sql ="select * from ss";
List<Emp> query = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : query) {
System.out.println(emp);
}
}
}
3、简单登陆页面
3.1 工具类
package Utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtils01 {
//定义成员变量
private static DataSource ds;
static{
try {
Properties prop = new Properties();
prop.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
public static DataSource getDataSource(){
return ds;
}
}
3.2定义对象
package com.itfenghua01;
import com.mysql.fabric.xmlrpc.base.Data;
import java.sql.Date;
public class Emp {
private Integer id;
private String name;
private String gender;
private Double salary;
private Date date;
private Integer dept_id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Integer getDept_id() {
return dept_id;
}
public void setDept_id(Integer dept_id) {
this.dept_id = dept_id;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", salary=" + salary +
", date=" + date +
", dept_id=" + dept_id +
'}';
}
}
3.3登陆页面
package com.itfenghua01;
import Utils.JDBCUtils;
import Utils.JDBCUtils01;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.Scanner;
public class DruidLogin {
public static void main(String[] args) {
//创建template对象
JdbcTemplate template = new JdbcTemplate(JDBCUtils01.getDataSource());
//输入信息
Scanner sc = new Scanner(System.in);
//查询语句
String sql ="select * from emp where id=? and name =?";
System.out.println("请输入你的id:");
String id = sc.nextLine();
System.out.println("请输入你的姓名:");
String name = sc.nextLine();
//执行SQL语句
Emp emp = null;
try {
//Emp为创建的类
emp = template.queryForObject(sql, new BeanPropertyRowMapper<Emp>(Emp.class),id,name);
System.out.println("登陆成功!");
//打印信息
System.out.println(emp);
} catch (DataAccessException e) {
//如果不存在则在此输出
System.out.println("你输入的id或姓名有误!");
}
}
}