簡單的用c3p0和dbutils實現的資料庫增刪查改
JdbcUtils.java
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JdbcUtils {
public QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
// 查詢所有資料
public ArrayList<Student> queryAll() {
ArrayList<Student> list = null;
String sql = "select * from student";
try {
//查詢所有資料,並將資料存入集合中
list = (ArrayList<Student>) runner.query(sql, new BeanListHandler<Student>(Student.class));
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//按學號查詢資料 返回student物件
public Student queryById() {
Student queryResult = null;
String sql = "select * from student where stuId=?";
try {
queryResult = runner.query(sql, new BeanHandler<Student>(Student.class), "20170831005");
} catch (SQLException e) {
e.printStackTrace();
}
return queryResult;
}
//更新資料
public void update(int math,String stuId){
String sql="update student set math=? where stuId=?";
try {
int rows = runner.update(sql, math,stuId);
if(rows>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//插入資料
public void insert(String stuId,String stuName,String stuSex,int chinese,int math,int english,int classId){
String sql="insert into student values(?,?,?,?,?,?,?)";
try {
int rows = runner.update(sql,stuId,stuName,stuSex,chinese,math,english,classId);
if (rows>0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Student.java
public class Student {
private String stuId;
private String stuName;
private String stuSex;
private int chinese;
private int math;
private int english;
private int classId;
public String getStuId() {
return stuId;
}
public void setStuId(String stuId) {
this.stuId = stuId;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public String getStuSex() {
return stuSex;
}
public void setStuSex(String stuSex) {
this.stuSex = stuSex;
}
public int getChinese() {
return chinese;
}
public void setChinese(int chinese) {
this.chinese = chinese;
}
public int getMath() {
return math;
}
public void setMath(int math) {
this.math = math;
}
public int getEnglish() {
return english;
}
public void setEnglish(int english) {
this.english = english;
}
public int getClassId() {
return classId;
}
public void setClassId(int classId) {
this.classId = classId;
}
@Override
public String toString() {
return "Student [stuId=" + stuId + ", stuName=" + stuName + ", stuSex=" + stuSex + ", chinese=" + chinese
+ ", math=" + math + ", english=" + english + ", classId=" + classId + "]";
}
}
Student.sql
CREATE TABLE `student` (
`stuId` varchar(11) NOT NULL,
`stuName` varchar(8) NOT NULL,
`stuSex` varchar(2) NOT NULL,
`chinese` int(3) DEFAULT NULL,
`math` int(3) DEFAULT NULL,
`english` int(3) DEFAULT NULL,
`classId` int(11) NOT NULL
)