jdbc连接数据库getConnection 增、删、改、查

SqlHelper.java连接数据库通用类... package org.jdbc.com.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SqlHelper { /** * 设置单例 */ public static SqlHelper instance = new SqlHelper(); /** * 设置单例的一个实例方法 * * @return */ public static SqlHelper getInstance() { if (instance == null) { synchronized (SqlHelper.class) { instance = new SqlHelper(); } } return instance; } /** * 得到MySql连接 * * @return */ public static Connection getMySqlConnection() { Connection conn = null; String url = "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8"; String user = "root"; String password = "root"; String driver="com.mysql.jdbc.Driver"; try { Class.forName(driver); conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { System.out.println("连接数据库出现异常" + e.getMessage()); } return conn; } /** * 得到MMSQL连接 * @return */ public static Connection getMMSQLConnection() { String url = "jdbc:sqlserver://localhost:1433;DatabaseName=jdbc"; String user = "sa"; String password = "sa"; String Driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"; Connection conn = null; try { Class.forName(Driver); conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { System.out.println("连接数据库出现异常" + e.getMessage()); } return conn; } /** * 得到Oracle连接 * @return */ public static Connection getOracleConnection() { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String user = "scott"; String password = "scott"; String Driver="oracle.jdbc.driver.OracleDriver"; Connection conn = null; try { Class.forName(Driver); conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { System.out.println("连接数据库出现异常" + e.getMessage()); } return conn; } /*** * 释放资源... * * @param rs * @param st * @param conn */ public static void Relesae(ResultSet rs, Statement st, Connection conn) { try { if (rs != null) rs.close(); if (st != null) st.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } -----------------------------------TestCRUD 类---------------=======================================================================测试类........ package org.jdbc.com.test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import junit.framework.TestCase; import org.jdbc.com.util.SqlHelper; @SuppressWarnings("all") public class TestCRUD extends TestCase { protected static ResultSet rs = null; protected static Statement st = null; protected static PreparedStatement ps = null; /** * Query 单元测试3.几就用方法用public 方法名用test开头...继承TestCase */ public static void testQuery() { Connection conn = null; conn = SqlHelper.getInstance().getMySqlConnection(); String sql = "select * from userinfo"; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { System.out.println(rs.getObject(1) + "\t" + rs.getObject(2)+"\t" +rs.getString("password")); } } catch (SQLException e) { e.printStackTrace(); } finally { SqlHelper.Relesae(rs, ps, conn); } } /** * add */ public static void testAdd() { PreparedStatement ps = null; Connection conn = null; int result = -1; conn = SqlHelper.getInstance().getMySqlConnection(); String sql = "INSERT INTO userinfo(id,username,PASSWORD) VALUES(?,?,?)"; try { /** */ ps = conn.prepareStatement(sql); ps.setString(1, null); ps.setString(2, "xiaoliang"); ps.setString(3, "1234"); result = ps.executeUpdate(); System.out.println("affect:" + result); } catch (SQLException e) { e.printStackTrace(); } finally { SqlHelper.Relesae(rs, ps, conn); } } /*** * update */ public static void testUpdate() { PreparedStatement ps = null; Connection conn = null; int result = -1; conn = SqlHelper.getInstance().getMySqlConnection(); String sql = "update userinfo set username=?,password=? where id=? "; System.out.println(sql); try { ps = conn.prepareStatement(sql); ps.setString(1, "xiaoliang"); ps.setString(2, "1234"); ps.setInt(3, 1); result = ps.executeUpdate(); System.out.println("affect:" + result); } catch (SQLException e) { e.printStackTrace(); } finally { SqlHelper.Relesae(rs, ps, conn); } } /*** */ public static void testDelete() { PreparedStatement ps = null; Connection conn = null; int result = -1; conn = SqlHelper.getInstance().getMySqlConnection(); String sql = "delete from userinfo where id=?"; try { ps = conn.prepareStatement(sql); ps.setInt(1, 1); result = ps.executeUpdate(); System.out.println("affect:" + result); } catch (SQLException e) { e.printStackTrace(); } finally { SqlHelper.Relesae(rs, ps, conn); } } } ConfigSQLHelper .java类.. package org.jdbc.com.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /** * 同过流去读取加载配置，灵活性会较好.. */ import org.apache.log4j.PropertyConfigurator; public class ConfigSQLHelper { protected static Properties pro = null; protected static InputStream is = null; protected static Connection conn = null; public static ConfigSQLHelper helper=new ConfigSQLHelper(); /** * 单例模式 * @return */ public static ConfigSQLHelper getInstance(){ return helper; } /** * 得到连接 * @return */ public static Connection getConnection() { String driver = ConfigSQLHelper.getConfigKey("driver"); String url = getConfigKey("url"); String username = getConfigKey("username"); String password =getConfigKey("password"); try { Class.forName(driver); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * 加载key * @param key * @return */ public static String getConfigKey(String key) { String strVal = ""; try { is = new FileInputStream(new File( "src/jdbc.properties")); pro = new Properties(); pro.load(is); PropertyConfigurator.configure(pro); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } strVal = (String) pro.getProperty(key); return strVal; } /** * 释放资源 */ public static void ReleaseSoucre(ResultSet rs, Statement stmt, Connection conn) { try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }