使用JDBC实现增删改查（加入Druid（德鲁伊）连接池）
使用jdbc（加入数据库连接池Druid）完成Student表的增删改查四个功能的操作
- 添加jdbc的连接驱动jar包
- 添加druidjar包
1. 创建数据库
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
sex VARCHAR(10) DEFAULT 'man',
age VARCHAR(5)
) CHARSET = utf8;
2. 编写Student.jsp实现类
package com.kaka.test_03;
public class Student {
private int id;
private String name;
private String sex;
private String age;
public Student() {
super();
}
public Student(int id, String name, String sex, String age) {
super();
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + "]";
}
}
3. 编写druid.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db0711?useUnicode=true&characterEncoding=utf8
username=root
password=root
#初始化连接
initialSize=10
#最大连接数量
maxActive=30
#最小空闲连接
minIdle=5
#超时等待时间
maxWait=5000
4. 编写JDBCUtil.java工具类
package com.kaka.test_03;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class JDBCUtils {
//获取数据库连接池
private static DataSource ds;
//提供静态代码块
//加载配置文件
static{
try{
//创建Properties集合类对象
Properties prop = new Properties();
//加载配置文件
InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//加载
prop.load(inputStream);
inputStream.close();
//创建连接池对象
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 ;
}
//释放资源
public static void close(Statement stmt,Connection conn) {
close(null,stmt,conn) ;
}
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5. 编写StuDaoImpl.java接口实现类
package com.kaka.test_03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class StuDaoImpl {
private Connection conn = null;
private PreparedStatement stmt = null;
private ResultSet rs = null;
//增加
public int insert(Student stu){
try {
//数据库连接对象
conn = JDBCUtils.getConnection();
//编写SQL语句
String sql = "insert into student (id,name,sex,age) values(?,?,?,?);";
//预编译对象
stmt = conn.prepareStatement(sql);
//执行更新
stmt.setInt(1, stu.getId());
stmt.setString(2, stu.getName());
stmt.setString(3, stu.getSex());
stmt.setString(4, stu.getAge());
int i = stmt.executeUpdate();
return i;
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.close(stmt, conn);
}
return 0;
}
//删除
public int delete (int id){
try {
conn = JDBCUtils.getConnection();
String sql = "delete from student where id = ?";
stmt = conn.prepareStatement(sql);
//获取id
stmt.setInt(1, id);
return stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.close(stmt, conn);
}
return 0;
}
//修改
public int update(Student stu){
try {
conn = JDBCUtils.getConnection();
String sql = "update student set name = ?,sex = ?,age = ? where id = ?";
stmt = conn.prepareStatement(sql);
//执行更新
stmt.setString(1, stu.getName());
stmt.setString(2, stu.getSex());
stmt.setString(3, stu.getAge());
stmt.setInt(4, stu.getId());
return stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.close(stmt, conn);
}
return 0;
}
//查询
public List<Student> selectAll(){
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,sex,age from student";
List<Student> stuList = new ArrayList<Student>();
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String sex = rs.getString("sex");
String age = rs.getString("age");
Student stu = new Student(id, name, sex, age);
//每封装完一个对象，添加到集合当中
stuList.add(stu);
}
return stuList;
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs, stmt, conn);
}
return null;
}
}
6. 编写Test.java测试类
package com.kaka.test_03;
import java.util.List;
public class Test {
public static void main(String[] args) {
StuDaoImpl sdi = new StuDaoImpl();
//新增
inserts(sdi);
//删除
deletes(sdi);
//修改
undates(sdi);
//查询
selects(sdi);
}
private static void selects(StuDaoImpl sdi) {
List<Student> stuList = sdi.selectAll();
stuList.forEach(System.out::println);
}
//修改
private static void undates(StuDaoImpl sdi) {
Student emp = new Student(1,"赵六","男","43");
int result = sdi.update(emp);
System.out.println(result);
}
//删除
private static void deletes(StuDaoImpl sdi) {
int result = sdi.delete(5);
System.out.println(result);
}
//新增
private static void inserts(StuDaoImpl sdi) {
Student stu = new Student(5,"name4","man","23");
int result = sdi.insert(stu);
if(result > 0){
System.out.println("success");
}else{
System.out.println("defeat");
}
}
}
7. 运行结果
7.1 查询数据
7.2 新增数据
7.3 删除数据
7.4 修改数据
