- 准备
- 新建Maven工程、配置依赖
- 代码
- Orace增删改查原代码
- 封装后的增删改查
- 代码优化
- 实现同时操作多个数据库
这里用的Maven，idea，oracle。对于使用mysql的一样可以看这个，除了加载驱动的名称、url和少部分sql语句有区别，其他的操作可以说几乎一样。
文章较长，但一定要多敲代码。
在创建一个maven项目，与之前的一致 JDBC的Maven项目创建
这里再添加个junit的依赖，用于测试代码。
junit junit 4.12
完整的pom.xml是这样的
4.0.0 com.symc jdbc-curd 1.0-SNAPSHOT 1.8 1.8 com.oracle ojdbc6 11.2.0.1.0 junit junit 4.12
别忘记修改编码和仓库位置，否则出现乱码问题和依赖代码报红。
点击转换，项目目录增加这些
先练习一下最基础的，这个一定是要会写的，代码编译后，可以到数据库中查看一下。
这里@Test的注解，就是junit提供的，可以直接运行当前的方法。建议使用@Test注解的类写到test目录下。
package com.symc.example2;
import org.junit.Test;
import java.sql.*;
public class Oracle_crud {
@Test
public void insert() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"scott",
"tiger");
String sql = "insert into dept values(seq_1.nextval,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "医学部");
ps.setString(2, "沈阳");
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("成功插入[" + i + "]条数据");
} else {
System.out.println("插入失败");
}
ps.close();
conn.close();
}
@Test
public void update() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"scott",
"tiger"
);
String sql = "update emp set sal = sal + ?, comm = comm + ? where " +
"ename = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDouble(1, 400);
ps.setDouble(2, 300);
ps.setString(3, "ALLEN");
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("成功修改" + i + "条数据");
} else {
System.out.println("修改失败");
}
ps.close();
conn.close();
}
@Test
public void insert2() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"scott",
"tiger"
);
String sql = "delete from emp where empno = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 7999);
int i = ps.executeUpdate();
if (i > 0) {
System.out.println("成功删除" + i + "条数据");
} else {
System.out.println("删除失败");
}
ps.close();
conn.close();
}
@Test
public void select() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"scott",
"tiger"
);
String sql = "select * from emp where ename like ? and job = ? and " +
"hireDate<=? and deptno = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,"%M%");
ps.setString(2,"CLERK");
ps.setDate(3, Date.valueOf("2021-9-29"));
ps.setInt(4,20);
ResultSet rs = ps.executeQuery();
while (rs.next()){
System.out.println(rs.getString(1)+ "t"
+rs.getString(2)+"t"
+ rs.getString(3) +"t"+rs.getInt(4));
}
System.out.println("完成");
rs.close();
ps.close();
conn.close();
}
}
封装后的增删改查
接下来我们创建的包要分成这三个，dao，javabean，utils。
dao包下放的是有关数据库操作的实现类和接口，比如实现增删改某一条数据或者查询某一个表的操作；javabean下存放的是数据库操作的实体，比如要操作一个表，一个视图等；utils下存放的是工具类，这种工具类是Oracle操作共用的，不是针对一个表或者一个视图的，比如连接数据库，关闭各种数据库对象这些操作。
首先我们在oracle的scott账户下创建一个病人表，用来当作例子。
这里有很多字段可以操作，比如number对应的Java中的int类型，varchar就对应String类型，date可以使用数据库厂商（这里是oracle）提供的类型。
//创建表的sql语句
create table patientinfo(
pid number(4) primary key,
pname varchar(10) not null,
sex char(2) default '男' check(sex='男' or sex='女') ,
age number(3,0),
tel varchar(11) unique,
address varchar(50) default '沈阳',
time date
);
在utils包下创建一个工具类，这里封装了连接数据库和关闭数据库的必备操作。
package com.symc.example3.utils;
import java.sql.*;
public class JDBCUtils {
private static String className = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String username = "scott";
private static String password = "tiger";
static {
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException throwable) {
throwable.printStackTrace();
throw new RuntimeException("数据库连接错误！");
}
}
public static void closeAll(Connection conn, Statement sta) {
if (sta != null) {
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void closeAll(ResultSet rs, Connection conn, Statement sta) {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (sta != null) {
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
然后再javabean包下创建要操作的实体——病人信息表。
把属性都私有化，并创建getter、setter方法，也可以加上构造器；重写toString方法以方便后面我们查看记录信息。
package com.symc.example3.javabean;
import java.sql.Date;
public class PatientInfo {
private int pid;
private String pname;
private String sex;
private int age;
private String tel;
private String address;
private Date time;//这里要注意导入的时间是那个包的
public PatientInfo(){};
public PatientInfo(int pid, String pname, String sex, int age, String tel,
String address, String time) {
this.pid = pid;
this.pname = pname;
this.sex = sex;
this.age = age;
this.tel = tel;
this.address = address;
this.time = Date.valueOf(time);
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getTime() {
return time;
}
public void setTime(Date time) {
this.time = time;
}
@Override
public String toString() {
return "PatientInfo{" +
"pid=" + pid +
", pname='" + pname + ''' +
", sex='" + sex + ''' +
", age=" + age +
", tel='" + tel + ''' +
", address='" + address + ''' +
", time=" + time +
'}';
}
}
创建封装增删改查的接口
package com.symc.example3.dao;
import com.symc.example3.javabean.PatientInfo;
import java.sql.SQLException;
public interface PatientInfoDao {
public int addPatientInfo(PatientInfo patientInfo) throws SQLException;
public int updatePatientInfo(PatientInfo patientInfo) throws SQLException;
public int deletePatientInfo(int pid) throws SQLException;
public PatientInfo[] selectPatientInfo(String pname) throws SQLException;
}
实现接口，即实现增删改查功能
package com.symc.example3.dao;
import com.symc.example3.javabean.PatientInfo;
import com.symc.example3.utils.JDBCUtils;
import javax.print.attribute.standard.PrinterName;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PatientInfoDaoImpl implements PatientInfoDao {
@Override
public int addPatientInfo(PatientInfo patientInfo) throws SQLException {
//1.连接
Connection conn = JDBCUtils.getConnection();
//2.创建sql语句
String sql = "insert into patientInfo values(?,?,?,?,?,?,?)";
//3.创建预编译对象
PreparedStatement ps = conn.prepareStatement(sql);
//4.给占位符设置值
ps.setInt(1,patientInfo.getPid());
ps.setString(2,patientInfo.getPname());
ps.setString(3,patientInfo.getSex());
ps.setInt(4,patientInfo.getAge());
ps.setString(5,patientInfo.getTel());
ps.setString(6,patientInfo.getAddress());
ps.setDate(7,patientInfo.getTime());
//5.执行sql语句
int i = ps.executeUpdate();
//5.关闭连接，释放资源
JDBCUtils.closeAll(conn,ps);
return i;
}
@Override
public int updatePatientInfo(PatientInfo patientInfo) throws SQLException {
Connection conn = JDBCUtils.getConnection();
String sql = "update patientInfo set pname = ? where pid = ? ";
PreparedStatement ps = conn.prepareStatement(sql);
// ps.setString(1,"pname");//这个占位符并不能设置字段名
ps.setString(1,patientInfo.getPname());
ps.setInt(2,patientInfo.getPid());
int i = ps.executeUpdate();
JDBCUtils.closeAll(conn,ps);
return i;
}
@Override
public int deletePatientInfo(int pid) throws SQLException {
Connection conn = JDBCUtils.getConnection();
String sql = "delete from patientInfo where pid = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,pid);
int i = ps.executeUpdate();
JDBCUtils.closeAll(conn,ps);
return i;
}
@Override
public PatientInfo[] selectPatientInfo(String name) throws SQLException {
//1.获取连接对象
Connection conn = JDBCUtils.getConnection();
//2.编写查询语句
String sql = "select * from patientInfo where pname = ?";
//3.创建预编译对象
//TYPE_SCROLL_SENSITIVE 设置游标可以双向滚动
//默认鼠标是无法双向滚动的，所以这里加入这个值1005这样我们就可以将游标放到最后
//CONCUR_UPDATABLE 可以更新ResultSet数据
PreparedStatement ps = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//4.给占位符设置值
ps.setString(1,name);
//5.执行sql语句并拿到结果集
ResultSet rs = ps.executeQuery();
//先将游标移动到最后
rs.last();
//获取行数
int length = rs.getRow();
//游标返回到最开始的位置
rs.beforeFirst();
//创建数组
PatientInfo[] pis = new PatientInfo[length];
int index = 0;
while (rs.next()){
//创建实体对象
PatientInfo pi = new PatientInfo();
pi.setPid(rs.getInt(1));
pi.setPname(rs.getString(2));
pi.setSex(rs.getString(3));
pi.setAge(rs.getInt(4));
pi.setTel(rs.getString(5));
pi.setAddress(rs.getString(6));
pi.setTime(rs.getDate(7));
//把封装好的实体放入对象数组
pis[index++] = pi;
}
//6.关闭
JDBCUtils.closeAll(rs,conn,ps);
//返回对象数组
return pis;
}
}
最后，就可以测试功能了，在test目录下创建测试类，并打上@Test
import com.symc.example3.dao.PatientInfoDaoImpl;
import com.symc.example3.dao.PatientInfoDaoImplPro;
import com.symc.example3.javabean.PatientInfo;
import com.symc.example3.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Date;
import java.sql.SQLException;
public class Oracle_CURD_Test {
PatientInfoDaoImpl pid = new PatientInfoDaoImpl();
@Test
public void insert() throws SQLException {
PatientInfo pi = new PatientInfo(1002, "张三", "男", 20, "16373894032",
"沈阳医学院", "2021-10-01");
int i = pid.addPatientInfo(pi);
if (i > 0) {
System.out.println("成功插入" + i + "条数据！");
} else {
System.out.println("插入失败！");
}
}
@Test
public void update() throws SQLException {
PatientInfo pi = new PatientInfo();
pi.setPid(1001);
pi.setPname("李四");
int i = pid.updatePatientInfo(pi);
if (i > 0) {
System.out.println("成功修改" + i + "条数据");
} else {
System.out.println("修改失败！");
}
}
@Test
public void delete() throws SQLException {
int i = pid.deletePatientInfo(1002);
if (i > 0) {
System.out.println("成功删除" + i + "条数据");
} else {
System.out.println("删除失败");
}
}
@Test
public void select() throws SQLException{
for (PatientInfo patientInfo : pid.selectPatientInfo("小明")) {
System.out.println(patientInfo.toString());
}
}
@Test
public void insertInBulk() throws SQLException {
PatientInfo pi = new PatientInfo();
pi.setPname("小明");
pi.setSex("男");
pi.setAge(20);
// pi.setTel("15521213320");//唯一约束，使得它为动态
pi.setAddress("沈阳医学院");
pi.setTime(Date.valueOf("2021-10-01"));
int startNo = 1005;//设置初始主键
int startTel = 10;
int count = 0;
int i = 0;
for (int j = 0; j < 5; j++) {
pi.setPid(startNo++);
pi.setTel("123456789"+ startTel++);
i = pid.addPatientInfo(pi);
count += i;
if (i > 0) {
System.out.println("成功插入" + i + "条数据！");
} else {
System.out.println("插入失败！");
}
}
System.out.println("共成功插入" + count + "条数据");
}
}
代码优化
我们之前的代码还可以优化，敲了很多代码后，我们发现增删改的操作其实都差不多，就sql语句不一致，所以可以把增删改的一些操作再封装到一起，查询操作很多不同，需要另外封装个方法。
具体操作是，在JDBCUtils类中添加这两个方法
public static int executeUpdate(String sql ,Object[] param) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
}
int i = ps.executeUpdate();
closeAll(conn,ps);
return i;
}
public static ResultSet executeQuery(Connection conn,PreparedStatement ps
,String sql,Object[] param) throws SQLException{
conn = getConnection();
ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
if (param != null) {
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
}
ResultSet rs = ps.executeQuery();
return rs;
}
接着再创建一个实现类，再重写一下接口。
package com.symc.example3.dao;
import com.symc.example3.javabean.PatientInfo;
import com.symc.example3.utils.JDBCUtils;
import javax.print.attribute.standard.PrinterName;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PatientInfoDaoImpl implements PatientInfoDao {
@Override
public int addPatientInfo(PatientInfo patientInfo) throws SQLException {
//1.连接
Connection conn = JDBCUtils.getConnection();
//2.创建sql语句
String sql = "insert into patientInfo values(?,?,?,?,?,?,?)";
//3.创建预编译对象
PreparedStatement ps = conn.prepareStatement(sql);
//4.给占位符设置值
ps.setInt(1,patientInfo.getPid());
ps.setString(2,patientInfo.getPname());
ps.setString(3,patientInfo.getSex());
ps.setInt(4,patientInfo.getAge());
ps.setString(5,patientInfo.getTel());
ps.setString(6,patientInfo.getAddress());
ps.setDate(7,patientInfo.getTime());
//5.执行sql语句
int i = ps.executeUpdate();
//5.关闭连接，释放资源
JDBCUtils.closeAll(conn,ps);
return i;
}
@Override
public int updatePatientInfo(PatientInfo patientInfo) throws SQLException {
Connection conn = JDBCUtils.getConnection();
String sql = "update patientInfo set pname = ? where pid = ? ";
PreparedStatement ps = conn.prepareStatement(sql);
// ps.setString(1,"pname");//这个占位符并不能设置字段名
ps.setString(1,patientInfo.getPname());
ps.setInt(2,patientInfo.getPid());
int i = ps.executeUpdate();
JDBCUtils.closeAll(conn,ps);
return i;
}
@Override
public int deletePatientInfo(int pid) throws SQLException {
Connection conn = JDBCUtils.getConnection();
String sql = "delete from patientInfo where pid = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,pid);
int i = ps.executeUpdate();
JDBCUtils.closeAll(conn,ps);
return i;
}
@Override
public PatientInfo[] selectPatientInfo(String name) throws SQLException {
//1.获取连接对象
Connection conn = JDBCUtils.getConnection();
//2.编写查询语句
String sql = "select * from patientInfo where pname = ?";
//3.创建预编译对象
//TYPE_SCROLL_SENSITIVE 设置游标可以双向滚动
//默认鼠标是无法双向滚动的，所以这里加入这个值1005这样我们就可以将游标放到最后
//CONCUR_UPDATABLE 可以更新ResultSet数据
PreparedStatement ps = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//4.给占位符设置值
ps.setString(1,name);
//5.执行sql语句并拿到结果集
ResultSet rs = ps.executeQuery();
//先将游标移动到最后
rs.last();
//获取行数
int length = rs.getRow();
//游标返回到最开始的位置
rs.beforeFirst();
//创建数组
PatientInfo[] pis = new PatientInfo[length];
int index = 0;
while (rs.next()){
//创建实体对象
PatientInfo pi = new PatientInfo();
pi.setPid(rs.getInt(1));
pi.setPname(rs.getString(2));
pi.setSex(rs.getString(3));
pi.setAge(rs.getInt(4));
pi.setTel(rs.getString(5));
pi.setAddress(rs.getString(6));
pi.setTime(rs.getDate(7));
//把封装好的实体放入对象数组
pis[index++] = pi;
}
//6.关闭
JDBCUtils.closeAll(rs,conn,ps);
//返回对象数组
return pis;
}
}
最后在测试类中测试代码
PatientInfoDaoImplPro pid1 = new PatientInfoDaoImplPro();
@Test
public void insert1() throws SQLException {
PatientInfo pi = new PatientInfo(1002, "张三", "男", 20, "16373894032",
"沈阳医学院", "2021-10-01");
int i = pid1.addPatientInfo(pi);
if (i > 0) {
System.out.println("成功插入" + i + "条数据！");
} else {
System.out.println("插入失败！");
}
}
@Test
public void update1() throws SQLException {
PatientInfo pi = new PatientInfo();
pi.setPid(1002);
pi.setPname("李四");
int i = pid1.updatePatientInfo(pi);
if (i > 0) {
System.out.println("成功修改" + i + "条数据");
} else {
System.out.println("修改失败！");
}
}
@Test
public void delete1() throws SQLException {
int i = pid1.deletePatientInfo(1002);
if (i > 0) {
System.out.println("成功删除" + i + "条数据");
} else {
System.out.println("删除失败");
}
}
@Test
public void select1() throws SQLException{
PatientInfo[] pis = pid1.selectPatientInfo("小明");
for (PatientInfo pi : pis) {
System.out.println(pi.toString());
}
}
实现同时操作多个数据库
假如现在本机的oracle和mysql都有一样的emp表，现在想要将一条数据插入到两个数据库，只需要写一份代码就能实现，那应该怎么做呢？
首先，依旧是建立三包，结构如下
先在javabean下创建实体类Emp（表）
package com.symc.example4.javabean;
import java.util.Date;
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hireDate;
private Double sal;
private Double comm;
private Integer deptno;
public Emp() {
}
public Emp(Integer empno, String ename, String job, Integer mgr,
Date hireDate, Double sal, Double comm, Integer deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hireDate = hireDate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Integer getMgr() {
return mgr;
}
public void setMgr(Integer mgr) {
this.mgr = mgr;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public Double getComm() {
return comm;
}
public void setComm(Double comm) {
this.comm = comm;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + ''' +
", job='" + job + ''' +
", mgr=" + mgr +
", hireDate=" + hireDate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
在dao包下创建一个数据库操作类的抽象类，后面Oracle实现类和Mysql实现类都是继承这个类。
package com.symc.example4.dao;
import com.symc.example4.javabean.Emp;
public abstract class DatabaseDao {
public abstract int insertEmp(Emp emp) throws Exception;
public abstract int updateEmp(Emp emp) throws Exception;
public abstract int deleteEmpByEmpNo(int empNo) throws Exception;
public abstract Emp[] selectEmpByCondition(Emp emp) throws Exception;
}
在utils包下创建两个数据库的工具类
package com.symc.example4.utils;
import java.sql.*;
public class JDBCUtils_oracle {
private static String className = "oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String username = "scott";
private static String password = "tiger";
static {
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException throwable) {
throwable.printStackTrace();
throw new RuntimeException("数据库连接错误！");
}
}
public static void closeAll(Connection conn, Statement sta) {
if (sta != null) {
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void closeAll(ResultSet rs, Connection conn, Statement sta) {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (sta != null) {
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static int executeUpdate(String sql ,Object[] param) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
}
int i = ps.executeUpdate();
closeAll(conn,ps);
return i;
}
public static ResultSet executeQuery(Connection conn,PreparedStatement ps
,String sql,Object[] param) throws SQLException{
conn = getConnection();
ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
if (param != null) {
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
}
ResultSet rs = ps.executeQuery();
return rs;
}
}
这个是mysql的
package com.symc.example4.utils;
import java.sql.*;
public class JDBCUtils_mysql {
private static String className = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/bookstore" +
"?characterEncoding=utf-8" ;
private static String username = "root";
private static String password = "1234";
static {
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException throwable) {
throwable.printStackTrace();
throw new RuntimeException("数据库连接错误！");
}
}
public static void closeAll(Connection conn, Statement sta) {
if (sta != null) {
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void closeAll(ResultSet rs, Connection conn, Statement sta) {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (sta != null) {
try {
sta.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static int executeUpdate(String sql ,Object[] param) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
}
int i = ps.executeUpdate();
closeAll(conn,ps);
return i;
}
public static ResultSet executeQuery(Connection conn,PreparedStatement ps
,String sql,Object[] param) throws SQLException{
conn = getConnection();
ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
if (param != null) {
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i]);
}
}
ResultSet rs = ps.executeQuery();
return rs;
}
}
分别在Oracle和Mysql实现抽象类DataDaseDao，
OracleDaoImpl、MyDaoImpl
这里只写OracleDaoImpl，两个基本是一样的操作
package com.symc.example4.dao;
import com.symc.example4.javabean.Emp;
import com.symc.example4.utils.JDBCUtils_oracle;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class OracleDaoImpl extends DatabaseDao {
@Override
public int insertEmp(Emp emp) throws Exception {
int i = JDBCUtils_oracle.executeUpdate("insert into emp values(?,?,?," +
"?,?," +
"?,?,?)",
new Object[]{emp.getEmpno(), emp.getEname(),
emp.getJob(), emp.getMgr(), emp.getHireDate(), emp.getSal(),
emp.getComm(), emp.getDeptno()
});
return i;
}
@Override
public int updateEmp(Emp emp) throws Exception {
int i = JDBCUtils_oracle.executeUpdate("update emp set sal = sal + ?,comm = " +
"comm + ? where ename = ?", new Object[]{
emp.getSal(), emp.getComm(), emp.getEname()
});
return i;
}
@Override
public int deleteEmpByEmpNo(int empNo) throws Exception {
int i = JDBCUtils_oracle.executeUpdate("delete from emp where empno = ?",
new Object[]{empNo});
return i;
}
@Override
public Emp[] selectEmpByCondition(Emp emp) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = JDBCUtils_oracle.executeQuery(conn, ps, "select * from emp where " +
"sal>?", new Object[]{emp.getSal()});
rs.last();
Emp[] emps = new Emp[rs.getRow()];
rs.beforeFirst();
int index = 0;
for (int i = 0; i < emps.length; i++) {
while (rs.next()){
Emp emp1 = new Emp();
emp1.setEmpno(rs.getInt(1));
emp1.setEname(rs.getString(2));
emp1.setJob(rs.getString(3));
emp1.setMgr(rs.getInt(4));
emp1.setHireDate(rs.getDate(5));
emp1.setSal(rs.getDouble(6));
emp1.setComm(rs.getDouble(7));
emp1.setDeptno(rs.getInt(8));
emps[index++] = emp1;
}
}
JDBCUtils_oracle.closeAll(rs,conn,ps);
return emps;
}
}
最后创建一个测试类测试一下
import com.symc.example4.dao.DatabaseDao;
import com.symc.example4.dao.OracleDaoImpl;
import com.symc.example4.javabean.Emp;
import org.junit.Test;
public class DatabaseTest {
DatabaseDao baseDao = new OracleDaoImpl();
@Test
public void insert() throws Exception{
Emp emp = new Emp();
emp.setEmpno(new Integer(9000));
emp.setEname("张三");
emp.setJob("CLERK");
emp.setMgr(7698);
emp.setHireDate(java.sql.Date.valueOf("2021-10-1"));
emp.setSal(3000d);
emp.setComm(500d);
emp.setDeptno(20);
int i = baseDao.insertEmp(emp);
System.out.println(i);
}
@Test
public void update() throws Exception{
Emp emp = new Emp();
emp.setSal(100d);
emp.setComm(200d);
emp.setEname("张三");
int i = baseDao.updateEmp(emp);
System.out.println(i);
}
@Test
public void delete() throws Exception{
Integer empNo = new Integer(9000);
int i = baseDao.deleteEmpByEmpNo(empNo);
System.out.println(i);
}
@Test
public void select() throws Exception{
Emp emp = new Emp();
emp.setSal(3000d);
Emp[] emps = baseDao.selectEmpByCondition(emp);
for (Emp emp1 : emps) {
System.out.println(emp1.toString());
}
}
}
