package test;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.tomcat.dbcp.dbcp.ConnectionFactory;
public class Test {
private static Properties prop;
private static Connection conn = null;
private static final String CONFIGNAME = "jdbc.properties";
public static void main(String[] args) {
getConnection();
try {
conn.setAutoCommit(false);
System.out.println("默认的事务隔离级别:" + conn.getTransactionIsolation());
Statement statement = conn.createStatement();
statement.executeUpdate("insert user(id, username, password) values(6, '张六', '6')");
System.out.println("Insert Success");
System.out.println("------------------ 我是分割线 ---------------------");
conn.commit();
String sql = "select * from user where id = 1";
ResultSet result = statement.executeQuery(sql_pojo);
while (result.next()) {
int id = result.getInt("id");
String username = result.getString("username");
String password = result.getString("password");
System.out.println("id: " + id + " username: " + username + " password: " + password);
}
result = statement.executeQuery(sql_pojo);
List<User> bean = getBeanList(result);
for (int i = 0; i < bean.size(); i++) {
System.out.println(bean.get(i).toString());
}
System.out.println("------------------ 我是分割线 ---------------------");
String sql_list = "select * from user";
result = statement.executeQuery(sql_list);
while (result.next()) {
int id = result.getInt("id");
String username = result.getString("username");
String password = result.getString("password");
System.out.println("id: " + id + " username: " + username + " password: " + password);
}
result = statement.executeQuery(sql_list);
List<User> list = getBeanList(result);
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i).toString());
}
} catch (SQLException e) {
if (conn != null){
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
} finally {
closeConnection();
}
}
* 获取数据库连接
* @throws SQLException
*/
public static Connection getConnection() {
prop = new Properties();
try {
prop.load(ConnectionFactory.class.getClassLoader().getResourceAsStream(CONFIGNAME));
Class.forName(prop.getProperty("jdbc_driverClassName"));
conn = DriverManager.getConnection(
prop.getProperty("jdbc_url"),
prop.getProperty("jdbc_username"),
prop.getProperty("jdbc_password"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
* 关闭数据库连接
* @throws SQLException
*/
public static void closeConnection() {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
* 获取实体类列表（将原生的数据库访问查询结果Result转为一个POJO）
* @param rs
* @return
* @throws SQLException
*/
public static <T> List<T> getBeanList(ResultSet rs) throws SQLException {
List retultList = resultSetToList(rs);
List<T> pojoList = new ArrayList<T>();
for(int i = 0; i < retultList.size(); i++) {
T t = (T) MapToBean(new User(), (Map) retultList.get(i));
pojoList.add(t);
}
return pojoList;
}
* 将ResultSet封装成list 而每条记录对应一个实体Map
* @param rs 结果集
* @return
* @throws SQLException
*/
public static List resultSetToList(ResultSet rs) throws SQLException{
if(rs == null) {
return null;
}
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
List list = new ArrayList();
Map rowData;
while (rs.next()){
rowData = new HashMap(columnCount);
for (int i = 1; i <= columnCount; i++){
rowData.put(md.getColumnName(i),rs.getObject(i));
}
list.add(rowData);
}
return list;
}
* @param bean 需要封装的vo
* @param map 需要转换的map
* @return 已经封装好数据的vo（object）
*/
public static Object MapToBean(Object bean, Map map) {
Map methods = new HashMap();
Method m[] = bean.getClass().getMethods();
for (int i = 0; i < m.length; i++) {
Method method = m[i];
String methodName = method.getName().toUpperCase();
methods.put(methodName, method);
}
Iterator it = null;
String key = "";
it = map.keySet().iterator();
while (it.hasNext()) {
key = (String) it.next();
String name = "GET" + key.toUpperCase();
if (methods.containsKey(name)) {
Method setMethod = (Method) methods.get("SET" + key.toUpperCase());
try {
if(setMethod!=null){
Object[] obj=null;
obj=new Object[1];
obj[0]=map.get(key);
setMethod.invoke(bean, obj);
}
else{
continue;
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return bean;
}
}
class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + "]";
}
}