java JDBC通用dao

原来没有写文章的习惯　　哎．．

　　学习这种东西时间长如果不用就记得不怎么清楚了，复习一下最基本的东西．记起一下．

package com.util.dao.jdbcDao;import java.io.InputStream;import java.sql.*;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;/** * jdbc DAO * @author Yanxinyu * */public class JdbcDao {public Connection conn = null; // 声明Connection对象的实例public Statement stmt = null; // 声明Statement对象的实例public PreparedStatement pstmt = null; // 声明PreparedStatement对象的实例public ResultSet rs = null; // 声明ResultSet对象的实例private static String dbClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";// 定义保存数据库驱动的变量private static String dbUrl = "jdbc:sqlserver://10.10.10.141:1433;DatabaseName=myBlog";// 连接数据库地址(SqlServer2005)private static String dbUser = "sa";// 用户名private static String dbPwd = "123";// 密码/** * 默认的不带参数的构造函数 */public JdbcDao() {// try {//// Class.forName(dbClassName);// 注册驱动// } catch (ClassNotFoundException e) {// e.printStackTrace();// }}/** * 带属性文件名的构造方法 *  * @param propertyFileName *            属性文件 */public JdbcDao(String propertyFileName) {Properties prop = new Properties();// 属性集合对象InputStream is = null;try {is = JdbcDao.class.getClassLoader().getResourceAsStream(propertyFileName);// 属性文件输入流// is = new FileInputStream("src/" + propertyFileName);prop.load(is);// 将属性文件流装载到Properties对象中is.close();// 关闭流dbClassName = prop.getProperty("dbClassName");dbUrl = prop.getProperty("dbUrl");dbUser = prop.getProperty("dbUser");dbPwd = prop.getProperty("dbPwd");} catch (Exception e) {System.out.println("属性文件  " + propertyFileName + " 打开失败！");}// try {// Class.forName(dbClassName);// 注册驱动// } catch (ClassNotFoundException e) {// e.printStackTrace();// }}/** * 创建数据库连接 *  * @return 返回已连接的connection对象 */public static Connection getConnection() {Connection conn = null;try {Class.forName(dbClassName);// 1.注册驱动conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);// 2.建立与数据库的链接System.out.println("连接" + conn.getCatalog() + "数据库成功!");} catch (Exception ee) {ee.printStackTrace();}if (conn == null) {System.err.println("警告: DbConnectionManager.getConnection() 获得数据库链接失败.\r\n\r\n链接类型:"+ dbClassName+ "\r\n链接位置:"+ dbUrl+ "\r\n用户/密码"+ dbUser + "/" + dbPwd);}return conn;}/** * 创建数据库连接 */public void getConnectionVoid() {Connection conn = null;try {Class.forName(dbClassName);// 注册驱动conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);// 建立与数据库的链接System.out.println("连接" + conn.getCatalog() + "数据库成功!");} catch (Exception ee) {ee.printStackTrace();}if (conn == null) {System.err.println("警告: DbConnectionManager.getConnection() 获得数据库链接失败.\r\n\r\n链接类型:"+ dbClassName+ "\r\n链接位置:"+ dbUrl+ "\r\n用户/密码"+ dbUser + "/" + dbPwd);}this.conn = conn;}/** * 关闭数据库的连接 释放资源 */public void close() {try { // 捕捉异常try {if (rs != null) { // 当ResultSet对象的实例rs不为空时rs.close(); // 关闭ResultSet对象}} finally {try {if (stmt != null) { // 当Statement对象的实例stmt不为空时stmt.close(); // 关闭Statement对象}} finally {if (conn != null) { // 当Connection对象的实例conn不为空时conn.close(); // 关闭Connection对象}}}System.out.println("释放资源成功! 关闭数据库连接!");} catch (Exception e) {e.printStackTrace(System.err); // 输出异常信息}}/** * 执行查询语句 *  * @param sql *            sql语句 * @return 返回List<Map> map中的key键为列名 value为值 */public List<Map> executeQueryListMap(String sql) {List<Map> list = new ArrayList();try { // 捕捉异常// conn = getConnection(); //// 调用getConnection()方法构造Connection对象的一个实例connstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);// 创建语句// pstmt=conn.prepareStatement(sql);rs = stmt.executeQuery(sql);// 执行查询while (rs.next()) {Map map = new HashMap();rs.getString(1);for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++)map.put(rs.getMetaData().getColumnName(i).toLowerCase(), rs.getString(i));// 取得列名(转换成小写) 列的值 存入maplist.add(map); // 把每一行的列名和值存入list}} catch (SQLException ex) {System.err.println(ex.getMessage()); // 输出异常信息}return list; // 返回结果集对象 结果处理}/** * 执行修改 添加 删除 */public int executeSql(String sql) {int result = 0; // 定义保存返回值的变量try { // 捕捉异常// conn = getConnection(); //// 调用getConnection()方法构造Connection对象的一个实例connstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);result = stmt.executeUpdate(sql); // 执行更新操作} catch (SQLException ex) {result = 0; // 将保存返回值的变量赋值为0}return result; // 返回保存返回值的变量}}

测试类

package com.util.dao.jdbcDao;import java.util.ArrayList;import java.util.List;import java.util.Map;/** * Jdbc测试类 * @author Yanxinyu * */public class JdbcTest {public static void main(String[] args) {JdbcDao jd=new JdbcDao();int returntmp=0;jd.getConnectionVoid();//查询//List<Map> list=jd.executeQueryListMap("select * from users");//for (Map map : list) {//System.out.println(map.get("id")+" "+map.get("loginid")+" "+map.get("loginpwd")+" "+map.get("name")+" "+map.get("qq")+" "+map.get("mail"));//}//修改//returntmp=jd.executeSql("update users set name='1yanxinyu' where id=68");//添加//returntmp=jd.executeSql("insert into  users values ('yanxinyu02','yanxinyu','yanxinyu','11111','[e-mail]')");//删除//returntmp=jd.executeSql("delete from users where id=70");System.out.println(returntmp==1?"成功":"失败");jd.close();}}

还有关于 statement 与preparestatement .的一些用法　这里就不记啦