android学习笔记5-JDBC编程

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();
		}
		
	}

	

}