package com.qyn;
import java.io.UnsupportedEncodingException;
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 javax.print.attribute.HashAttributeSet;
import javax.swing.RepaintManager;
import model.UserInfo;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
public class jdbcUtils {
/**
* @param args
* 数据库操作类,连接的数据库为mySQL
*
*/
private final String USERNAME = "root";
private final String PASSWORD = "1";
private final String DRIVER = "com.mysql.jdbc.Driver";//定义数据库的驱动信息
private final String URL = "jdbc:mysql://localhost:3306/mydb2";
private Connection connection;
private PreparedStatement preparedStatement;//定义访问数据库的地址
private ResultSet resultSet;//定义查询结果的返回集合
public jdbcUtils() {
try {
Class.forName(DRIVER);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//连接数据库
public Connection getConnection() {
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
//数据库的插入,更新,删除
public boolean updateByPreparedStatement(String sql, List<Object> params)
throws SQLException {
boolean flag = false;
int result = -1;
preparedStatement = connection.prepareStatement(sql);
int index = 1;
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
preparedStatement.setObject(index++, params.get(i));
}
}
result = preparedStatement.executeUpdate();
flag = result > 0 ? true : false;
return flag;
}
public Map<String, Object> findSimpleResult(String sql, List<Object> params)
throws SQLException {
Map<String, Object> map = new HashMap<String, Object>();
int index = 1;
preparedStatement = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
preparedStatement.setObject(index++, params.get(i));
}
}
resultSet = preparedStatement.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;
}
public List<Map<String, Object>> findMoreResult(String sql,
List<Object> params) throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
preparedStatement = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
preparedStatement.setObject(index++, params.get(i));
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metadata = resultSet.getMetaData();
int col_len = metadata.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
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);
}
list.add(map);
}
return list;
}
// JDBC的封装可以用反射机制,访问数据库,sql为要执行的SQL语句,params为要填充的占位符,cls为实例化T的类型
public <T> T findSimpleRefResult(String sql, List<Object> params,
Class<T> cls) throws Exception {
T resultObject = null;
int index = 1;
preparedStatement = connection.prepareStatement(sql);
//填充占位符
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
preparedStatement.setObject(index++, params.get(i));
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metadata = resultSet.getMetaData();
int col_len = metadata.getColumnCount();
//将resultSet转化为map
while (resultSet.next()) {
resultObject = cls.newInstance();
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 = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true);
field.set(resultObject, cols_value);
}
}
return resultObject;
}
public <T> List<T> findMoreRefResult(String sql, List<Object> params,
Class<T> cls) throws Exception {
List<T> list = new ArrayList<T>();
int index = 1;
preparedStatement = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
preparedStatement.setObject(index++, params.get(i));
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metadata = resultSet.getMetaData();
int col_len = metadata.getColumnCount();
while (resultSet.next()) {
T resultObject = cls.newInstance();
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 = "";
}
Field field = cls.getDeclaredField(cols_name);
field.setAccessible(true);
field.set(resultObject, cols_value);
}
list.add(resultObject);
}
return list;
}
public void releaseConn() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
jdbcUtils utils = new jdbcUtils();
utils.getConnection();
/* 插入数据 */
// String sqlString = "delete from user where name=?";
// List<Object> param2 = new ArrayList<Object>();
// param2.add("rose");
//
// try {
// boolean flag = utils.updateByPreparedStatement(sqlString, param2);
// System.out.println(flag);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
/* 查询单条数据 */
// String sql="select * from user where id=?";
// List<Object> param2 = new ArrayList<Object>();
// param2.add(1);
// try {
// Map<String,Object>map=utils.findSimpleResult(sql, param2);
// System.out.println(map);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
/* 查询多条数据 */
// String sql="select * from user";
// try {
// List<Map<String,Object>> map=utils.findMoreResult(sql, null);
// System.out.println(map);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }finally{
// utils.releaseConn();
// }
/* 反射机制查询单条 记录 */
// String sql = "select * from user where id=?";
// List<Object> param2 = new ArrayList<Object>();
// param2.add(1);
// try {
// UserInfo userInfo = utils.findSimpleRefResult(sql, param2,
// UserInfo.class);
// System.out.println(userInfo);
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } finally {
// utils.releaseConn();
// }
/* 反射机制查询多条记录 */
// String sql = "select * from reply where comm_id=?";
// List<Object> param2 = new ArrayList<Object>();
// param2.add(1);
// try {
// List<Reply> userInfo = utils.findMoreRefResult(sql, param2,
// Reply.class);
// System.out.println(userInfo);
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } finally {
// utils.releaseConn();
// }
//
String sql = "insert into article(art_title,poster_name,art_content,post_time) values(?,?,?,?)";
List<Object> param2 = new ArrayList<Object>();
try {
param2.add("李钟硕的小动作".getBytes("utf-8"));
param2.add("朴勋".getBytes("utf-8"));
param2.add("当用户点击按钮时,调出固定大小的文件管理器,默认显示的目录是SD卡的根目录。用户选择文件后,在界面上显示该文件的路径。".getBytes("utf-8"));
param2.add("2014/05/21");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
utils.updateByPreparedStatement(sql, param2);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}