JDBC连接数据库

结构

记得要先导入连接数据库的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包来使用！