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();
}
}
}
/*
MySQL Data Transfer
Source Host: localhost
Source Database: jdbc
Target Host: localhost
Target Database: jdbc
Date: 2011/10/10 17:06:50
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for userinfo
-- ----------------------------
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL auto_increment,
`password` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`birthday` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `userinfo` VALUES ('2', 'dddd', 'ddd', null);
INSERT INTO `userinfo` VALUES ('3', 'dddd', 'ljl', null);
INSERT INTO `userinfo` VALUES ('4', 'ggg', '1234', null);
INSERT INTO `userinfo` VALUES ('5', 'ds', 'ds', null);
INSERT INTO `userinfo` VALUES ('6', 'ds', 'ds', null);
INSERT INTO `userinfo` VALUES ('7', '1234', 'gg', null);
源代码