结构
记得要先导入连接数据库的jar包!
1.创建jdbcConfig.properties文件
用来存储连接数据库时用到的参数
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.1.30:3306/login
username=root
password=123456
2.创建一个类来存储文件中的键值对(ConfigUtil.java)
package com.jdbc.util;
import java.util.ResourceBundle;
/**
* 配置文件
* @author admin
*
*/
public class ConfigUtil {
private static ResourceBundle bundle;
public static String driverClass;
public static String url;
public static String username;
public static String password;
public static int pageSize;
static{
bundle = ResourceBundle.getBundle("jdbcConfig");
driverClass = bundle.getString("driverClass");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
pageSize = Integer.parseInt(bundle.getString("pageSize"));
}
}
*3.创建数据库访问类(DaoHandle.java)
package com.jdbc.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;
/**
* 数据访问辅助类
* @author admin
*
*/
public class DaoHandler {
//加载驱动
static{
try {
Class.forName(ConfigUtil.driverClass);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 连接数据库
* @return
*/
public static Connection getConnection(){
Connection con = null;
try {
con = DriverManager.getConnection(ConfigUtil.url,ConfigUtil.username,ConfigUtil.password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
/**
* 关闭连接
* @param con
* @param pstmt
* @param rs
*/
public static void close(Connection con,PreparedStatement pstmt,ResultSet rs){
try {
if(con!=null){
con.close();
}
if(pstmt!=null){
pstmt.close();
}
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 执行DML
* @param sql 执行的SQL语句
* @param params SQL注入至占位符中的数据
*/
public static void executeUpdate(String sql, Object[] params){
Connection con = null;
PreparedStatement pstmt = null;
con = getConnection();
if(con!=null){
//注入参数
try {
pstmt = con.prepareStatement(sql);
if(params!=null){
for(int i = 0;i<params.length;i++){
pstmt.setObject(i+1, params[i]);
}
}
pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
close(con, pstmt, null);
}
}
}
/**
* 执行DQL语句
* @param sql
* @param params
* @param objClass
* @return
*/
public static <T> List<T> executeQuery(String sql,Object[] params,Class<T> objClass){
List<T> array = new ArrayList<T>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
con = getConnection();
if(con!=null){
try {
pstmt = con.prepareStatement(sql);
if(params!=null){
for(int i = 0;i<params.length;i++){
pstmt.setObject(i+1, params[i]);
}
}
rs = pstmt.executeQuery();
//解析结果集
FieldInfo[] fieldArray = DaoResultSet.parseResultSet(rs);
//反射objClass的所有属性
Set<Field> fieldSet = ReflectUtil.getFields(objClass);
//反射objClass的所有方法
Set<Method> methodSet = ReflectUtil.getMethods(objClass);
//获取结果集
while(rs.next()){
//反射创建对象
T t = objClass.newInstance();
for(FieldInfo fieldInfo:fieldArray){
//获取列名
String fieldName = fieldInfo.getFieldName();
//获取类的索引
int index = fieldInfo.getIndex();
//根据列名找到属性
Object value = null;
Field f = ReflectUtil.getFildByName(fieldSet, fieldName);
if(f!=null){
Class type = f.getType();
if(type == String.class){
value = rs.getString(index);
}
else if(type == Integer.class || type == int.class){
value = rs.getInt(index);
}
else if(type == Double.class || type == double.class){
value = rs.getDouble(index);
}
else if(type == Timestamp.class){
value = rs.getTimestamp(index);
}
else if(type == Date.class){
try {
value = rs.getTimestamp(index);
} catch (Exception e) {
// TODO Auto-generated catch block
value = null;
}
}
else if(type == Float.class || type == float.class){
value = rs.getFloat(index);
}
//得到对象所对应的set方法
Method m = ReflectUtil.getMethodByName(methodSet, fieldName);
//将值写入对象
if(m!=null){
m.invoke(t, value);
}
}
}
array.add(t);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
close(con, pstmt, rs);
}
}
return array;
}
/**
* 分页查询
* @param sql
* @param params
* @param ojbClass
* @param page
* @return
*/
public static <T> List<T> executeQueryPage(String sql,Object[] params,Class<T> objClass,int page){
List<T> array = new ArrayList<T>();
Connection con = getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
if(con!=null){
try {
sql = sql+" limit ?,?";
pstmt = con.prepareStatement(sql);
int pageIndex;
if(params!=null){
pageIndex = params.length+1;
for(int i =0;i<params.length;i++){
pstmt.setObject(i+1, params[i]);
}
}
else{
pageIndex = 1;
}
//注入分页参数
pstmt.setObject(pageIndex,(page-1)*ConfigUtil.pageSize);
pstmt.setObject(pageIndex+1, ConfigUtil.pageSize);
rs = pstmt.executeQuery();
//得到结果集
FieldInfo[] fieldArray = DaoResultSet.parseResultSet(rs);
Set<Field> fieldSet = ReflectUtil.getFields(objClass);
Set<Method> methodSet = ReflectUtil.getMethods(objClass);
while(rs.next()){
T t = objClass.newInstance();
for(FieldInfo fieldInfo:fieldArray){
String fieldName = fieldInfo.getFieldName();
int index = fieldInfo.getIndex();
Field f= ReflectUtil.getFildByName(fieldSet, fieldName);
Object value = null;
if(f!=null){
Class type = f.getType();
if(type == String.class){
value = rs.getString(index);
}
else if(type == Integer.class || type == int.class){
value = rs.getInt(index);
}
else if(type == Double.class || type == double.class){
value = rs.getDouble(index);
}
else if(type == Timestamp.class){
value = rs.getTimestamp(index);
}
else if(type == Date.class){
value = rs.getDate(index);
}
else if(type == Float.class || type == float.class){
value = rs.getFloat(index);
}
Method m = ReflectUtil.getMethodByName(methodSet, fieldName);
if(m!=null){
m.invoke(t, value);
}
}
}
array.add(t);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return array;
}
/**
* 查询单行数据
* @param sql
* @param params
* @param objClass
* @return
*/
public static <T> T executeQueryOneRow(String sql, Object[] params, Class<T> objClass){
List<T> list = executeQuery(sql, params, objClass);
if(!list.isEmpty()){
return list.get(0);
}
return null;
}
/**
* 查询单值数据
*/
public static Object executeQuerySingle(String sql, Object[] params){
Object value = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
con = getConnection();
if(con != null){
try {
pstmt = con.prepareStatement(sql);
//注入参数
if(params != null){
for(int i = 0; i < params.length; i++){
pstmt.setObject(i+1, params[i]);
}
}
//执行查询
rs = pstmt.executeQuery();
while(rs.next()){
value = rs.getObject(1);
}
}
catch(Exception e){
e.printStackTrace();
}
finally{
close(con, pstmt, rs);
}
}
return value;
}
}
4.数据表字段信息(FieldInfo.class)
package com.jdbc.util;
/**
* 数据表字段信息
* @author admin
*
*/
public class FieldInfo {
//字段名
private String fieldName;
//索引
private int index;
public FieldInfo(){
}
public FieldInfo(String fieldName,int index){
super();
this.fieldName = fieldName;
this.index = index;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
}
5.解析结果集(DaoResultSet.class)
package com.jdbc.util;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/**
* 解析结果集
* @author admin
*
*/
public class DaoResultSet {
public static FieldInfo[] parseResultSet(ResultSet rs){
FieldInfo[] array = null;
//获得元数据
try {
ResultSetMetaData metaData = rs.getMetaData();
//根据字段长度创建数组
array = new FieldInfo[metaData.getColumnCount()];
for(int i =0;i<array.length;i++){
//获取列的字段名和索引值
array[i] = new FieldInfo(metaData.getColumnName(i+1),i+1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return array;
}
}
6.反射工具类(ReflectUtil.java)
package com.jdbc.util;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashSet;
import java.util.Set;
/**
* 反射工具类,封装了常用的反射方法
* @author admin
*
*/
public class ReflectUtil {
/**
* 获得类中的所有属性
* @param objClass
* @return
*/
public static <T> Set<Field> getFields(Class<T> objClass){
Set<Field> set = new HashSet<>();
Field[] fields1 = objClass.getFields();
Field[] fields2 = objClass.getDeclaredFields();
for(Field f: fields1){
set.add(f);
}
for(Field f:fields2){
set.add(f);
}
return set;
}
/**
* 获得类种的所有方法
* @param objClass
* @return
*/
public static <T> Set<Method> getMethods(Class<T> objClass){
Set<Method> set = new HashSet<>();
Method[] methods1 = objClass.getMethods();
Method[] methods2 = objClass.getDeclaredMethods();
for(Method m:methods1){
set.add(m);
}
for(Method m:methods2){
set.add(m);
}
return set;
}
/**
* 根据名称查找属性对象
* @param set
* @param fieldName
* @return
*/
public static Field getFildByName(Set<Field> set,String fieldName){
for(Field f:set){
if(f.getName().equalsIgnoreCase(fieldName)){
return f;
}
}
return null;
}
/**
* 根据字段名查找相应的set方法-
* @param set
* @param fieldName
* @return
*/
public static Method getMethodByName(Set<Method> set,String fieldName){
for(Method m:set){
if(m.getName().equalsIgnoreCase("set"+fieldName)){
return m;
}
}
return null;
}
}
7.执行查询
首先需要创建一个类来跟数据库的表相对应,这边用一个用户类,属性是用户名和密码
package com.pojo;
public class User {
private String userName;
private String passWord;
public User(){
}
public User(String userName, String passWord) {
super();
this.userName = userName;
this.passWord = passWord;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
}
现在就可以执行相关的查询了,这边列举两条基本的查询和插入操作
//根据用户名查找密码
List<User> array=null;
array=DaoHeandle.excuteQuery("select passWord from user where userName = ?", new Object[]{name}, User.class);
//添加新用户
DaoHeandle.executeUpdate("insert user values(?,?)", new Object[]{name,passWord});
总结:
使用该方法可以更方便的对数据库进行增删该查的操作,可以打包成jar包来使用!