package com.wjf.helper; import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties; public class JdbcHelper {
//TODO 这些以后改成读xml，通过工厂方式实现就可以实现通用软编码了 // 表示定义数据库的用户名
private final String USERNAME = "root";
// 定义数据库的密码
private final String PASSWORD = "123";
// 定义数据库的驱动信息
private final String DRIVER = "com.mysql.jdbc.Driver";
// 定义访问数据库的地址
private final String URL = "jdbc:mysql://182.180.50.118:3306/test";
// 定义数据库的链接
private Connection connection;
// 定义sql语句的执行对象
private PreparedStatement pstmt;
// 定义查询返回的结果集合
private ResultSet resultSet;
// 是否使用事务
private boolean useTrans = false; /**
* @param 是否使用事务，当使用事务的时候必须要提交
* @throws ClassNotFoundException
* @throws SQLException
*/
public JdbcHelper(boolean useTrans) throws ClassNotFoundException,
SQLException {
this.useTrans = useTrans;
Class.forName(DRIVER);
connection = (Connection) DriverManager.getConnection(URL, USERNAME,
PASSWORD);
//设置使用是否自动提交，即是否使用事务
connection.setAutoCommit(!useTrans);
} /**
* 事务回滚
* 异常时要事务回滚
* @throws SQLException
*/
public void rollBack() throws SQLException {
if (useTrans)
connection.rollback();
} /**
* 执行Sql，如del insert update
*
* @param sql
* @param params
* @return 返回影响的行数
* @throws SQLException
*/
public int executeSql(String sql, List params) throws SQLException {
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i+1, params.get(i));
}
}
return pstmt.executeUpdate();
} /**
* @param sql
* @param params
* @return
* @throws SQLException
*/
public List<HashMap<String, Object>> getDataMany(String sql, List params)
throws SQLException {
List list = new ArrayList();
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i+1, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map map = new HashMap();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
} /**
* 查询单条记录
* @param sql
* @param params
* @return
* @throws SQLException
*/
public Map<String, Object> getDataOne(String sql, List<Object> params) throws SQLException{
Map<String, Object> map = new HashMap<String, Object>();
int index = 1;
pstmt = connection.prepareStatement(sql);
if(params != null && !params.isEmpty()){
for(int i=0; i<params.size(); i++){
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();//返回查询结果
ResultSetMetaData metaData = resultSet.getMetaData();
int col_len = metaData.getColumnCount();
while(resultSet.next()){
for(int i=0; i<col_len; i++ ){
String cols_name = metaData.getColumnName(i+1);
Object cols_value = resultSet.getObject(cols_name);
if(cols_value == null){
cols_value = "";
}
map.put(cols_name, cols_value);
}
}
return map;
} /**通过反射机制查询单条记录
* @param sql
* @param params
* @param cls
* @return
* @throws Exception
*/
public <T> T getObjectOne(String sql, List params,
Class<T> cls )throws Exception{
T resultObject = null;
int index = 1;
pstmt = connection.prepareStatement(sql);
if(params != null && !params.isEmpty()){
for(int i = 0; i<params.size(); i++){
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while(resultSet.next()){
//通过反射机制创建一个实例
resultObject = cls.newInstance();
for(int i = 0; i<cols_len; i++){
String cols_name = metaData.getColumnName(i+1);
Object cols_value = resultSet.getObject(cols_name);
if(cols_value == null){
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true); //打开javabean的访问权限
field.set(resultObject, cols_value);
}
}
return resultObject; } /**通过反射机制查询多条记录
* @param sql
* @param params
* @param cls
* @return
* @throws Exception
*/
public <T> List<T> getObjectMany(String sql, List<Object> params,
Class<T> cls )throws Exception {
List<T> list = new ArrayList<T>();
int index = 1;
pstmt = connection.prepareStatement(sql);
if(params != null && !params.isEmpty()){
for(int i = 0; i<params.size(); i++){
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while(resultSet.next()){
//通过反射机制创建一个实例
T resultObject = cls.newInstance();
for(int i = 0; i<cols_len; i++){
String cols_name = metaData.getColumnName(i+1);
Object cols_value = resultSet.getObject(cols_name);
if(cols_value == null){
cols_value = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true); //打开javabean的访问权限
field.set(resultObject, cols_value);
}
list.add(resultObject);
}
return list;
} /**
*释放资源
*没有.net的dispose好麻烦呀
*/
public void release() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (useTrans)
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map; import com.wjf.helper.JdbcHelper; public class T { /**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
JdbcHelper helper = new JdbcHelper(true);
// 查询一个对象
List<Object> listP = new ArrayList<Object>();
listP.add(1);
Role role1 = helper.getObjectOne(
"select * from wjf_role where ROLE_NO=?", Arrays.asList("1"),
Role.class);
Role role = helper.getObjectOne(
"select * from wjf_role where ROLE_NO=?", listP, Role.class);
System.out.println(String.format("%s %s", role1.ROLE_NAME,
role1.ROLE_NO));
// 查询多个对象
List<Role> roleList = helper.getObjectMany("select * from wjf_role",
null, Role.class);
for (Role r : roleList) {
System.out.println(String.format("%s %s", r.ROLE_NAME,
role.ROLE_NO));
}
helper.executeSql("insert into wjf_role(ROLE_NAME) values(?)",
Arrays.asList("AA22"));
helper.executeSql("insert into wjf_role(ROLE_NAME) values(?)",
Arrays.asList("AA33"));
helper.executeSql("insert into wjf_role(ROLE_NAME) values(?)",
Arrays.asList("AA44"));
List<HashMap<String, Object>> list = helper.getDataMany(
"select * from wjf_role where role_no >?", Arrays.asList(0));
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map;
map = list.get(i);
System.out.println(String.format("%s %s", map.get("ROLE_NO"),
map.get("ROLE_NAME")));
}
helper.release();
// System.out.println(list.size()); }
}