使用Statement执行sql增、删、改、查语句是最基础的操作，接下来我将自己总结的代码分享给广大的程序猿们。
想要完成sql的增、删、改、查，必须成功连接数据库，连接数据库方法请点击：http://www.jianshu.com/p/5695f77f7826。表中的所有数据来自Oracle数据库(下载地址：http://pan.baidu.com/s/1c2cWF52) 中自带的SCOTT表。
项目结构图
项目结构图.png
实体类
package com.chiansoft.vo;
import java.util.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private double sal;
private double comm;
private int deptno;
public Emp() {
super();
}
public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
super();
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 Emp(String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
super();
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int 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 int getMgr() {
return mgr;
}
public void setMgr(int 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 int getDeptno() {
return deptno;
}
public void setDeptno(int 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接口
package com.chinasoft.dao;
import java.util.List;
import com.chiansoft.vo.Emp;
public interface EmpDao {
/**
* 作者：_借东西的小人
* 向表中增加员工信息
*/
boolean addEmp(Emp emp);
/**
* 通过empno删除员工信息
*/
boolean deleteByEmpno(int empno);
/**
* 通过ename删除员工信息
*/
boolean deleteByEname(String ename);
/**
* 通过empno修改员工信息
*/
boolean update(Emp emp,int empno);
/**
* 查询所有员工信息
*/
List<Emp> QueryEmp();
/**
* 通过ename查询员工信息
*/
List<Emp> QueryEmpByEname(String ename);
}
工具类
package com.chiansoft.dbUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtils {
public static Connection getConnection(String user,String password){
Connection conn = null;
try {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.获取连接对象
String url = "jdbc:oracle:thin:@localhost:1521:xe";
conn = DriverManager.getConnection
(url,"SCOTT","TIGER");
//
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(ResultSet rs,Statement stat,Connection conn){
try {
// 6.关闭连接
if(rs!=null){
rs.close();
}
if(stat!=null){
stat.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
dao接口的实现
package com.chiansoft.dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.chiansoft.dbUtils.DBUtils;
import com.chiansoft.vo.Emp;
import com.chinasoft.dao.EmpDao;
public class EmpDaoImpl implements EmpDao{
// 数据库用户名
String user = "SCOTT";
// 数据库密码
String password = "TIGER";
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
int count = 0;
List<Emp> list = new ArrayList<Emp>();
/**
* 向表中增加员工信息
*/
@Override
public boolean addEmp(Emp emp) {
try {
// 1.加载驱动
// 2.获取连接对象
conn = DBUtils.getConnection(user, password);
// 3.编写sql语句
String sql = "insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) "
+ "values ("+emp.getEmpno()+",'"+emp.getEname()+"','"+emp.getJob()+"',"
+ ""+emp.getMgr()+",to_date('"+String.valueOf(emp.getHiredate())+"','YYYY-MM-DD'),"
+ ""+emp.getSal()+","+emp.getComm()+","+emp.getDeptno()+")";
// 4.创建Statement对象
stat = conn.createStatement();
// 5.执行sql语句
count = stat.executeUpdate(sql);
System.out.println("成功插入信息"+count+"条");
} catch (SQLException e) {
e.printStackTrace();
}finally{
// 6.关闭连接
DBUtils.close(null, stat, conn);
}
return count==0?false:true;
}
/**
* 通过empno删除员工信息
*/
@Override
public boolean deleteByEmpno(int empno) {
try {
conn = DBUtils.getConnection(user, password);
String sql = "delete from emp where empno="+empno+"";
stat = conn.createStatement();
count = stat.executeUpdate(sql);
System.out.println("成功删除记录"+count+"条");
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.close(null, stat, conn);
}
return count==0?false:true;
}
/**
* 通过ename删除员工信息
*/
@Override
public boolean deleteByEname(String ename) {
try {
conn = DBUtils.getConnection(user, password);
String sql = "delete from emp where ename='"+ename+"'";
stat = conn.createStatement();
count = stat.executeUpdate(sql);
System.out.println("成功删除记录"+count+"条");
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.close(null, stat, conn);
}
return count==0?false:true;
}
/**
* 通过empno修改员工信息
*/
@Override
public boolean update(Emp emp,int empno) {
try {
conn = DBUtils.getConnection(user, password);
String sql = "update emp set ename='"+emp.getEname()+"',job='"+emp.getJob()+"',"
+ "mgr="+emp.getMgr()+",hiredate=to_date('"+String.valueOf(emp.getHiredate())+"','YYYY-MM-DD'),"
+ "sal="+emp.getSal()+",comm="+emp.getComm()+",deptno="+emp.getDeptno()+" "
+ "where empno = "+empno+"";
stat = conn.createStatement();
count = stat.executeUpdate(sql);
System.out.println("成功修改记录"+count+"条");
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.close(null, stat, conn);
}
return count==0?false:true;
}
/**
* 查询所有员工信息
*/
@Override
public List<Emp> QueryEmp() {
try {
conn = DBUtils.getConnection(user, password);
String sql = "select * from emp";
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()){
int empno = rs.getInt(1);
String ename = rs.getString(2);
String job = rs.getString(3);
int mgr = rs.getInt(4);
Date hiredate = rs.getDate(5);
double sal = rs.getDouble(6);
double comm = rs.getDouble(7);
int deptno = rs.getInt(8);
Emp emp = new Emp(empno,ename, job, mgr, hiredate, sal, comm, deptno);
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.close(rs, stat, conn);
}
return list;
}
/**
* 通过ename查询员工信息
*/
@Override
public List<Emp> QueryEmpByEname(String ename) {
try {
conn = DBUtils.getConnection(user, password);
String sql = "select * from emp where ename='"+ename+"'";
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()){
int empno = rs.getInt(1);
String ename1 = rs.getString(2);
String job = rs.getString(3);
int mgr = rs.getInt(4);
Date hiredate = rs.getDate(5);
double sal = rs.getDouble(6);
double comm = rs.getDouble(7);
int deptno = rs.getInt(8);
Emp emp = new Emp(empno,ename1, job, mgr, hiredate, sal, comm, deptno);
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.close(rs, stat, conn);
}
return list;
}
}
测试类
package com.chinasoft.test;
import java.sql.Date;
import java.util.List;
import com.chiansoft.dao.impl.EmpDaoImpl;
import com.chiansoft.vo.Emp;
import com.chinasoft.dao.EmpDao;
public class EmpTest {
public static void main(String[] args) {
EmpDao dao = new EmpDaoImpl();
Emp emp = new Emp();
System.out.println("添加员工测试");
emp.setEmpno(1114);
emp.setEname("lilili");
emp.setJob("Cliker");
emp.setHiredate(Date.valueOf("2017-05-05"));
emp.setSal(1111);
emp.setComm(100);
emp.setDeptno(10);
dao.addEmp(emp);
System.out.println("通过empno删除员工信息测试");
dao.deleteByEmpno(1111);
System.out.println("通过ename删除员工信息测试");
dao.deleteByEname("lilili");
System.out.println("通过empno修改员工信息测试");
emp.setEname("韩跑跑");
emp.setJob("Mouse");
emp.setHiredate(Date.valueOf("1999-9-9"));
emp.setSal(6666);
emp.setComm(66);
emp.setDeptno(10);
dao.update(emp,1000);
System.out.println("查询所有员工信息测试");
List<Emp> list = dao.QueryEmp();
for(Emp empInfo:list){
System.out.println(empInfo);
}
System.out.println("通过ename查询员工信息测试");
List<Emp> list1 = dao.QueryEmpByEname("韩跑跑");
for(Emp empInfo:list1){
System.out.println(empInfo);
}
}
}
完整的项目代码已经上传到github中了，访问地址:github
QQ截图20170919143859.png
在学习的过程中如果遇到什么问题，欢迎大家提问。
