package com.codeguy.util;
import java.lang.reflect.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.codeguy.domain.Users;
public class DBUtil {
public static void main(String[] args){
//String[] values = {"fanhao","haohao"};
//add("insert into users(username,password)values(?,?)",values);
Users user = new Users();
user.setId(23);
user.setUsername("fanhao");
user.setPassword("haohao");
List<Object> list = query(user,0,5);
for(int i=0; i<list.size(); i++){
Users u = (Users)list.get(i);
System.out.println(u.getId()+" -- " + u.getUsername() + " -- "+u.getPassword());
}
//增,刪,分頁查 實現了 大體測試了一下 沒啥問題..... 我只用到三個 所以就寫了三個..
//一些鸟异常 就直接抛了...
}
private static Connection conn = null;
private static PreparedStatement pst = null;
private static ResultSet rs = null;
private static Connection getConnection(){
try {
Class.forName("com.mysql.jdbc.Driver");
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/study","root","haohao");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
private static String getTableName(Object obj){
String className = obj.getClass().getName();
String tableName = className.substring(className.lastIndexOf(".")+1,className.length());
return tableName;
}
public static void add(Object obj){
StringBuffer fields = new StringBuffer();
StringBuffer values = new StringBuffer();
String[] fieldsName = getFieldName(obj);
for(int i=0; i<fieldsName.length; i++){
fields.append(fieldsName[i]);
values.append("?");
if(i != fieldsName.length-1){
fields.append(",");
values.append(",");
}
}
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO "+getTableName(obj)+"(");
sql.append(fields+")");
sql.append("VALUES("+values+")");
getConnection();
try {
conn.setAutoCommit(false);
pst = conn.prepareStatement(sql.toString());
for(int i=0; i<fieldsName.length; i++){
pst.setObject(i+1,getFieldValues(obj, fieldsName[i].toString()));
}
pst.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally{
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
close();
}
}
public static int delete(Object obj,String primary){
String[] fields = getFieldName(obj);
StringBuffer rule = new StringBuffer();
for(int i=0; i<fields.length; i++){
Object temp = getFieldValues(obj, fields[i]);
if(temp != null){
if(getFieldType(obj, fields[i]).equals(String.class)){
rule.append(" "+fields[i]+"='"+temp+"' ");
}else{
rule.append(" "+fields[i]+"="+temp+" ");
}
if(i!=fields.length-1){
rule.append(" AND ");
}
}
}
getConnection();
try {
int key = 0;
pst = conn.prepareStatement("SELECT id FROM "+getTableName(obj)+" WHERE "+rule.toString());
rs = pst.executeQuery();
if(rs.next()){
key = rs.getInt(1);
}
if(key != 0)
pst.executeUpdate("DELETE FROM "+getTableName(obj)+" WHERE "+primary+"="+key);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close();
}
return 0;
}
public static List<Object> query(Object obj,int start,int end){
List<Object> list = new ArrayList<Object>();
String sql = "SELECT * FROM "+getTableName(obj)+" limit "+start+", "+end;
getConnection();
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
String[] fields = getFieldName(obj);
Class cls = obj.getClass();
Method method = null;
while(rs.next()){
Object o = null;
try {
o = cls.newInstance();
for(int i=0; i<fields.length; i++){
try {
method = cls.getMethod("set"+upCase(fields[i]),getFieldType(obj, fields[i]));
try {
method.invoke(o, rs.getObject(fields[i]));
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException 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();
}
list.add(o);
}
}catch(SQLException ex){
ex.printStackTrace();
}finally{
close();
}
return list;
}
private static void close(){
try{
if(rs != null){
rs.close();
}
rs = null;
if(pst != null){
pst.close();
}
pst = null;
if(conn != null){
conn.close();
}
conn = null;
}catch(SQLException ex){
ex.printStackTrace();
}
}
private static String[] getFieldName(Object obj){
Class cls = obj.getClass();
Field fields[] = cls.getDeclaredFields();
String[] fieldNames = new String[fields.length];
for(int i=0; i<fields.length; i++){
fieldNames[i] = fields[i].getName();
}
return fieldNames;
}
private static Object getFieldValues(Object obj,String fieldName){
Class cls = obj.getClass();
Object value = null;
Method methodGet = null;
try {
methodGet = cls.getMethod("get"+upCase(fieldName));
try {
value = methodGet.invoke(obj);
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return value;
}
private static String upCase(String str) {
char fristCh = str.charAt(0);
if (Character.isUpperCase(fristCh)) {
return str;
}
String buf = str.substring(1);
StringBuffer retStr = new StringBuffer();
retStr.append((char) (fristCh - 0x20));
retStr.append(buf);
return retStr.toString();
}
private static Class getFieldType(Object obj,String name){
Class cls = obj.getClass();
Field field = null;
try {
field = cls.getDeclaredField(name);
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return field.getType();
}
}
没用Hibernate 练手型的写了个DBUtil类
最新推荐文章于 2023-12-13 11:53:00 发布