- 博客(0)
- 资源 (2)
- 收藏
- 关注
RSS订阅servlet制作网站
package com.xr.util;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import org.junit.Test;
import com.xr.dto.ManagerDTO;
public class DBHelper {
private Connection conn = null;
{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("创建驱动失败");
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/xc","root", "root");
} catch (SQLException e) {
System.out.println("创建连接失败");
e.printStackTrace();
}
}
// 单表新增，修改，删除
public boolean excuteUpdate(String sql) {
if (conn == null)
return false;
boolean sign = false;
try {
Statement stat = conn.createStatement();
int i = stat.executeUpdate(sql);
if (i > 0) {
sign = true;
} else {
sign = false;
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
return sign;
}
}
// 多表新增，修改，删除
public boolean excuteUpdate(Object... sql) {
if (conn == null)
return false;
boolean sign = false;
try {
conn.setAutoCommit(false);
Statement stat = conn.createStatement();
for (Object o : sql) {
stat.addBatch(o.toString());
}
stat.executeBatch();
conn.commit();
sign = true;
} catch (Exception ex) {
conn.rollback();
sign = false;
ex.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
conn.close();
} catch (SQLException e) {
}
return sign;
}
}
@Test
public void tet() throws SQLException{
List list=(List) conn.createStatement().executeQuery("select name from tbl_manager where B_id='1'");
ManagerDTO m=(ManagerDTO) list.get(0);
System.out.println("aaaaaaa"+m.getName());
}
// 单表查询
public List excuteQuery(String sql, String objName) {
if (conn == null)
return null;
List list = new ArrayList();
try {
Statement stat = conn.createStatement();
ResultSet re = stat.executeQuery(sql);
Class c = Class.forName(objName);
Method[] ms = c.getDeclaredMethods();
ResultSetMetaData rsmd=re.getMetaData();
while (re.next()) {
Object obj = c.newInstance();
for (int i = 0; i < ms.length; i++) {
Method m = ms[i];
if (m.getName().startsWith("set")) {
String name = m.getName().substring(3);
for(int j=1;j<=rsmd.getColumnCount();j++){
if(name.equalsIgnoreCase(rsmd.getColumnName(j))){
m.invoke(obj, re.getString(name)==null?"":re.getString(name));
break;
}
}
}
}
list.add(obj);
}
} catch (Exception ex) {
list = null;
ex.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
return list;
}
}
/*标量结果*/
public Object executeScare(String sql){
if (conn == null)
return null;
Object obj = null;
try {
Statement stat = conn.createStatement();
ResultSet re = stat.executeQuery(sql);
if (re.next()) {
obj=re.getString(1);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
return obj;
}
}
// 单个对象的查询
public Object excuteOneQuery(String sql, String objName) {
if (conn == null)
return null;
Object obj = null;
try {
Statement stat = conn.createStatement();
ResultSet re = stat.executeQuery(sql);
Class c = Class.forName(objName);
Method[] ms = c.getDeclaredMethods();
ResultSetMetaData rsmd = re.getMetaData();
if (re.next()) {
obj = c.newInstance();
for (int i = 0; i < ms.length; i++) {
Method m = ms[i];
if (m.getName().startsWith("set")) {
String name = m.getName().substring(3);
for(int j=1;j<=rsmd.getColumnCount();j++){
if(name.equalsIgnoreCase(rsmd.getColumnName(j))){
m.invoke(obj, re.getString(name)==null?"":re.getString(name));
break;
}
}
}
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
return obj;
}
}
// 多表查询
public List excuteQuery(String sql) {
if (conn == null)
return null;
List list = new ArrayList();
try {
Statement stat = conn.createStatement();
ResultSet re = stat.executeQuery(sql);
ResultSetMetaData remd = re.getMetaData();
while (re.next()) {
List row = new ArrayList();
for (int i = 1; i < remd.getColumnCount() + 1; i++) {
row.add(re.getString(i)==null?"":re.getString(i));
}
list.add(row);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
return list;
}
}
// 多表查询,单条记录返回
public List excuteOneQuery(String sql) {
if (conn == null)
return null;
List row = new ArrayList();
try {
Statement stat = conn.createStatement();
ResultSet re = stat.executeQuery(sql);
ResultSetMetaData remd = re.getMetaData();
if (re.next()) {
for (int i = 1; i < remd.getColumnCount() + 1; i++) {
row.add(re.getString(i));
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
}
return row;
}
}
}
2013-04-28
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人