接着上一次JAVA之JDBC工具类简单版,新手的话可以先看看简单版(点击简单版即可跳转的简单版博客)再回来看这个进阶版。还是那句话,不对之处还请各位不吝赐教。
在开始之前,在项目中引入mysql的资源包,否则后面的代码会出现很多报错!务必引入,该包自行百度下载即可
在项目的目录下创建config文件夹,在里面新建文件保存jdbc的配置信息,这样做便于修改
jdbc.properties内容:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/xianfeng?useUnicode=true&characterEncoding=utf8
user=root
password=root
xianfeng就是对应数据库的名字
用户和密码要改成自己的哦
在src下新建一个util包,分别对应四个类Config,DateUtils,jdbcTest,jdbcUtils
Config.java用于获取配置信息
DateUtils.java用于将字符串的日期转化问Date格式,这样才能插入数据库
jdbcTest.java后面用于增删改查的测试
jdbcUtils.java用于数据库方法的封装(重点)
Config.java代码:
package util;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
public class Config {
private Properties prop = new Properties();
public Config(String file)
{
try
{
InputStream in = new FileInputStream(file);
prop.load(in);
}
catch (IOException e)
{
e.printStackTrace();
}
}
public String getValue(String key)
{
return prop.getProperty(key);
}
}
DateUtils.java代码:
package util;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtils {
private static DateFormat d = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
private static DateFormat d2 = new SimpleDateFormat("yyyy-MM-dd");
public static String getDateStr() {
return d.format(new Date());
}
public static String getDateStr(Date date) {
return d.format(date);
}
public static Date getDate(String dateStr) {
try {
Date date = d2.parse(dateStr);
return date;
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
}
jdbcUtils代码:
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import rowsMapper.RowsMapper;
public class JdbcUtils {
private static String driverClass ;
private static String url ;
private static String user ;
private static String password ;
static{
Config config = new Config("config/jdbc.properties");
driverClass = config.getValue("driver");
url = config.getValue("url");
user = config.getValue("user");
password = config.getValue("password");
/**
* 驱动注册
*/
try {
Class.forName(driverClass);//注册加载驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取 Connetion
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 关闭ResultSet资源
* @param rs
*/
public static void close(ResultSet rs){
if(null != rs){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭Statement资源
* @param stmt
*/
public static void close(Statement stmt){
if(null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭close资源
* @param conn
*/
public static void close(Connection conn){
if(null != conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭资源
* @param rs
* @param stmt
* @param conn
*/
public static void close(ResultSet rs,Statement stmt,Connection conn){
close(rs);
close(stmt);
close(conn);
}
/**
* insert方法
* @param conn
* @param sql
* @param parems
* @return
* @throws SQLException
*/
public static int insert(Connection conn,String sql,Object[] parems) throws SQLException{
PreparedStatement stmt = null;
try{
stmt = conn.prepareStatement(sql);
if(null != parems && parems.length>0){//判断是否有信息增加
for(int i = 0;i<parems.length;i++){
stmt.setObject(i+1, parems[i]);//使用给定对象设置指定参数的值
}
}
int count = stmt.executeUpdate();
return count;
}finally{
close(stmt);
}
}
/**
* 泛型查询方法
* @param conn
* @param sql
* @param paramsList
* @param rm
* @return
* @throws SQLException
*/
public static <T> List<T> executeQuery(Connection conn,String sql,Object[] paramsList,RowsMapper<T> rm) throws SQLException{
PreparedStatement stmt = null;
ResultSet rs = null;
List<T> list = new ArrayList<T>();
try{
stmt = conn.prepareStatement(sql);
if(null != paramsList && paramsList.length>0){//判断是否有信息增加
for(int i = 0;i<paramsList.length;i++){
stmt.setObject(i+1, paramsList[i]);
}
}
rs = stmt.executeQuery();
while(rs.next()){
T t = rm.getEntity(rs);
list.add(t);
}
return list;
}finally{
close(rs);
close(stmt);
}
}
}
在项目下新建一个entity包,里面放实体类Doctor
Doctor.java代码:
package entity;
import java.util.Date;
public class Doctor {
private int id ;
//医生编号
private String doctorNo;
//医生姓名
private String doctorName;
//医生性别
private String doctorSex;
//医生电话
private String doctorTel;
//科室名字
private String sectionName;
//医生学历
private String doctorEduction;
//医生简介
private String doctorIntroduce;
//医生入职时间
private Date dootorHiredate;
//医生年龄
private int doctorAge;
//医生身份证号
private String doctorPcard;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getDoctorNo() {
return doctorNo;
}
public void setDoctorNo(String doctorNo) {
this.doctorNo = doctorNo;
}
public String getDoctorName() {
return doctorName;
}
public void setDoctorName(String doctorName) {
this.doctorName = doctorName;
}
public String getDoctorSex() {
return doctorSex;
}
public void setDoctorSex(String doctorSex) {
this.doctorSex = doctorSex;
}
public String getDoctorTel() {
return doctorTel;
}
public void setDoctorTel(String doctorTel) {
this.doctorTel = doctorTel;
}
public String getSectionName() {
return sectionName;
}
public void setSectionName(String sectionName) {
this.sectionName = sectionName;
}
public String getDoctorEduction() {
return doctorEduction;
}
public void setDoctorEduction(String doctorEduction) {
this.doctorEduction = doctorEduction;
}
public String getDoctorIntroduce() {
return doctorIntroduce;
}
public void setDoctorIntroduce(String doctorIntroduce) {
this.doctorIntroduce = doctorIntroduce;
}
public Date getDootorHiredate() {
return dootorHiredate;
}
public void setDootorHiredate(Date dootorHiredate) {
this.dootorHiredate = dootorHiredate;
}
public int getDoctorAge() {
return doctorAge;
}
public void setDoctorAge(int doctorAge) {
this.doctorAge = doctorAge;
}
public String getDoctorPcard() {
return doctorPcard;
}
public void setDoctorPcard(String doctorPcard) {
this.doctorPcard = doctorPcard;
}
}
继续在项目下面新建rowsMapper包和服务层的两个包server.dao,server.dao.impl
rowsMapper主要是将数据结果rs中的属性赋值给对象,然后返回该对象
server.dao,server.dao.impl主要用于封装对象的增删改查方法
rowsMapper包中RowsMapper代码:
package rowsMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 转换为行对象的接口
* @param <T>
*/
public interface RowsMapper<T> {
public T getEntity(ResultSet rs) throws SQLException;
}
rowsMapper包中DoctorRowsMapper代码:
package rowsMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import entity.Doctor;
public class DoctorRowsMapper implements RowsMapper<Doctor> {
public Doctor getEntity(ResultSet rs) throws SQLException{
Doctor doctor = new Doctor();
doctor.setId(rs.getInt("D_ID"));
doctor.setDoctorNo(rs.getString("D_No"));
doctor.setDoctorName(rs.getString("D_Name"));
doctor.setDoctorSex(rs.getString("D_Sex"));
doctor.setDoctorTel(rs.getString("D_Tel"));
doctor.setSectionName(rs.getString("S_Name"));
doctor.setDoctorEduction(rs.getString("D_Education"));
doctor.setDoctorIntroduce(rs.getString("D_Introduce"));
doctor.setDootorHiredate(rs.getDate("D_hiredate"));
doctor.setDoctorAge(rs.getInt("D_Age"));
doctor.setDoctorPcard(rs.getString("D_Pcard"));
return doctor;
}
}
server.dao包中IDoctorDao代码:
package server.dao;
import entity.Doctor;
public interface IDoctorDao {
/**
* 根据登陆账号找到医生信息
*/
public Doctor queryByDoctorNo(String DoctorNo) ;
/**
* 添加员工
*/
public void add(Doctor doctorEntity);
/**
* 根据ID删除信息
*/
public void delete(int[] id);
/**
* 修改信息
*/
public void edit(Doctor doctorEntity);
}
server.dao.impl包中DoctorDaoImpl代码:
package server.dao.impl;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import entity.Doctor;
import rowsMapper.DoctorRowsMapper;
import rowsMapper.RowsMapper;
import server.dao.IDoctorDao;
import util.JdbcUtils;
public class DoctorDaoImpl implements IDoctorDao {
@Override
public Doctor queryByDoctorNo(String DoctorNo) {
Connection conn = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from t_doctor where D_No = ?";
Object[] paramsList= {DoctorNo};
RowsMapper<Doctor> rm = new DoctorRowsMapper();
List<Doctor> list = JdbcUtils.executeQuery(conn, sql, paramsList, rm);
if(null !=list &&list.size() > 0) {
return list.get(0);
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}finally {
JdbcUtils.close(conn);
}
return null;
}
@Override
public void add(Doctor doctorEntity) {
Connection conn = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into t_doctor (D_ID,D_No,D_Name,D_Age,D_Sex,D_Tel,D_Pcard,S_Name,D_Education,D_Introduce,D_hiredate)";
sql += " value (?,?,?,?,?,?,?,?,?,?,?)";
Object[] params = { null,doctorEntity.getDoctorNo(),
doctorEntity.getDoctorName(), doctorEntity.getDoctorAge(),
doctorEntity.getDoctorSex(), doctorEntity.getDoctorTel(),
doctorEntity.getDoctorPcard(), doctorEntity.getSectionName(),
doctorEntity.getDoctorEduction(),doctorEntity.getDoctorIntroduce(),doctorEntity.getDootorHiredate() };
JdbcUtils.insert(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(conn);
}
}
@Override
public void delete(int[] id) {
Connection conn = null;
try {
conn = JdbcUtils.getConnection();
String sql = "delete from t_doctor where 1 = 1";
sql += " and D_ID in (";
Object[] params = new Object[id.length];
for (int i = 0; i < id.length; i++) {
if (i == id.length - 1) {
sql += " ?";
} else {
sql += " ?,";
}
params[i] = id[i];
}
sql += " )";
JdbcUtils.insert(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(conn);
}
}
@Override
public void edit(Doctor doctorEntity) {
Connection conn = null;
try {
conn = JdbcUtils.getConnection();
String sql = "update t_doctor set D_ID = ?,D_No = ?,D_Name = ?,"
+ "D_Age = ?,D_Sex = ?,D_Tel = ?,D_Pcard = ?,S_Name = ?,"
+ "D_Education = ?,D_Introduce = ?,D_hiredate = ? where D_ID = ?";
Object[] params = { doctorEntity.getId(),doctorEntity.getDoctorNo(),doctorEntity.getDoctorName(),
doctorEntity.getDoctorAge(),doctorEntity.getDoctorSex(),
doctorEntity.getDoctorTel(),doctorEntity.getDoctorPcard(),
doctorEntity.getSectionName(),doctorEntity.getDoctorEduction(),
doctorEntity.getDoctorIntroduce(),doctorEntity.getDootorHiredate(),
doctorEntity.getId()};
JdbcUtils.insert(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(conn);
}
}
}
server.dao中的思路主要是在对象接口中声明对象的数据库方法,这里就举例Doctor对象的增删改查,然后在server.dao.impl中实现对应接口的方法,后面在jdbcTest通过接口回调即可调用写好的方法
接下来就是在jdbcTest进行测试
查询测试:
package util;
import entity.Doctor;
import server.dao.IDoctorDao;
import server.dao.impl.DoctorDaoImpl;
public class jdbcTest {
public static void main(String[] args) {
IDoctorDao doctorDao = new DoctorDaoImpl();
Doctor doctor = doctorDao.queryByDoctorNo("a001");
System.out.println(doctor.getDoctorName());
System.out.println(doctor.getDoctorAge());
System.out.print(doctor.getDoctorTel());
}
}
测试结果:
数据库:
增加测试(插入新数据):
package util;
import entity.Doctor;
import server.dao.IDoctorDao;
import server.dao.impl.DoctorDaoImpl;
public class jdbcTest {
public static void main(String[] args) {
IDoctorDao doctorDao = new DoctorDaoImpl();
Doctor doctor = new Doctor();
doctor.setDoctorNo("a005");
doctor.setDoctorName("小白");
doctor.setDoctorAge(24);
doctor.setDoctorSex("男");
doctor.setDoctorTel("66666668888");
doctor.setDoctorPcard("441381199911113344");
doctor.setSectionName("儿科");
doctor.setDoctorEduction("本科");
doctor.setDoctorIntroduce("是一个程序员");
doctor.setDootorHiredate(DateUtils.getDate("2019-1-10"));
doctorDao.add(doctor);
}
}
数据库:
删除测试:
package util;
import server.dao.IDoctorDao;
import server.dao.impl.DoctorDaoImpl;
public class jdbcTest {
public static void main(String[] args) {
IDoctorDao doctorDao = new DoctorDaoImpl();
int a[] = {4};
doctorDao.delete(a);
}
}
数据库:
修改测试:
package util;
import entity.Doctor;
import server.dao.IDoctorDao;
import server.dao.impl.DoctorDaoImpl;
public class jdbcTest {
public static void main(String[] args) {
IDoctorDao doctorDao = new DoctorDaoImpl();
Doctor doctor = doctorDao.queryByDoctorNo("a003");
doctor.setDoctorName("小基");
doctorDao.edit(doctor);
}
}
数据库:
从数据库的结果图都可以看出测试都是通过的,不管有没看懂都敲一遍吧,这样你基本就掌握了JDBC的增删改查了,还是挺实用的呢