方便java操作数据库在任何地方只需要简短的一句话即可实现对数据库操作
创建info.properties文件, 保存数据库信息
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3307 /test
username = root
password =1234
工具类 提供连接服务
import java.sql.*;
import java.util.ResourceBundle;
/**
*
* @author cyh
*数据库工具类
*/
public class DBUtil {
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
ResourceBundle rb = ResourceBundle.getBundle("info" );
driverClass = rb.getString("driverClass" );
url = rb.getString("url" );
username = rb.getString("username" );
password = rb.getString("password" );
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection () throws Exception{
return DriverManager.getConnection(url,username,password);
}
public static void closeAll (ResultSet rs , PreparedStatement pre, Connection conn){
if (rs != null ){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pre != null ){
try {
pre.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null ){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
帮助类,提供具体增删改查方法
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.cyh.util.DBUtil;
public class DAO <T > {
ResultSet res = null ;
Connection conn = null ;
PreparedStatement pre = null ;
private void setParameter(String... parameter){
for (int i=0 ; i<parameter.length; i++){
try {
pre.setObject(i+1 , parameter[i]);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public List<T> query(Class<T> cls , String sql, String... parameter){
List<T> list = new ArrayList<>();
try {
conn = DBUtil.getConnection();
pre = conn.prepareStatement(sql);
setParameter(parameter);
res = pre.executeQuery();
while (res.next()){
T obj = cls.newInstance();
setData(cls, obj);
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtil.closeAll(res, pre, conn);
}
return list;
}
/**插入/删除**/
public int Update(String sql, String... parameter){
int num = 0 ;
try {
conn = DBUtil.getConnection();
pre = conn.prepareStatement(sql);
setParameter(parameter);
num = pre.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtil.closeAll(res, pre, conn);
}
return num;
}
private void setData(Class<T> cls, T obj){
/**
* Field 对象属性 类,
* 通过方法 set(Object obj, Object value) ; 给obj设置其value值
* 通过ResultSet对象 得到ResultSetMetaData接口的对象, 可以获得数据库字段属性
*/
try {
ResultSetMetaData rsmd = res.getMetaData();
int col = rsmd.getColumnCount();
for (int i=1 ; i<=col; i++){
String DBField = rsmd.getColumnLabel(i);
Field field = cls.getDeclaredField(DBField);
field.setAccessible(true );
field.set(obj, res.getObject(i));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
创建user类进行测试
public class User {
private Integer userId;
private String userName;
private Integer userAge;
public Integer getUserId () {
return userId;
}
public void setUserId (Integer userId) {
this .userId = userId;
}
public String getUserName () {
return userName;
}
public void setUserName (String userName) {
this .userName = userName;
}
public Integer getUserAge () {
return userAge;
}
public void setUserAge (Integer userAge) {
this .userAge = userAge;
}
public String toString (){
return getUserId()+"," +getUserName()+"," +getUserAge();
}
}
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import com.cyh.bean.Book;
import com.cyh.bean.Time;
import com.cyh.bean.User;
import DAO.DAO;
public class Test {
public static void main(String[] args) {
User user = new User();
new DAO().Update("insert into t_user values(?,?,?)" , "12344" ,"张三" ,"19" );
new DAO().Update("update t_user set ID = ? where ID = ?" , "1000" , "12344" );
List<User> list = new DAO().query(user.getClass(), "select ID userId, NAME userName, AGE userAge from t_user" );
new DAO().Update("DELETE FROM t_user WHERE ID=?" , "12344" );
}
}