jdbc常用

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cn.itcast.jdbc.datasource.MyDataSource2;

public final class JdbcUtils {

private static String url = "jdbc:sqlserver://localhost;databaseName=SSMS";
private static String user = "sa";
private static String password = "password";
private static MyDataSource2 myDataSource = new MyDataSource2();
//private static String url = "jdbc:odbc:SSMS";
//Connection conn = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=SSMS", "sa", "password");
private JdbcUtils(){

}
static{
try {
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}

public static Connection getConnection() throws SQLException{
//return DriverManager.getConnection(url);
//return DriverManager.getConnection(url, user, password);
return myDataSource.getConnection();
}

public static void free(ResultSet rs, Statement st, Connection conn){
try {
if (rs != null)
rs.close();
} catch(SQLException e){
e.printStackTrace();
}finally {
try {
if(st != null)
st.close();
} catch(SQLException e){
e.printStackTrace();
} finally {
if(conn != null)
try {
conn.close();
//myDataSource.free(conn);
} catch (Exception e) {
e.printStackTrace();
}
}

}
}
}

----------------------------------

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class JdbcUtilsSingle {

private String url = "jdbc:odbc:SIMS";
//private static JdbcUtilsSingle instance = new JdbcUtilsSingle(); //预加载
private static JdbcUtilsSingle instance = null;
private JdbcUtilsSingle(){

}

public static JdbcUtilsSingle getInstance(){
if(instance == null) { //延迟加载
synchronized(JdbcUtilsSingle.class){ //懒加载可能会有并发的问题，因此最好加锁
if(instance == null) { //这种加锁方式相当于在方法上加锁，
instance = new JdbcUtilsSingle(); //但在方法上定义成同步每次调用时，
} //成本很高，因此缩小加锁范围。
}
}
return instance;
}
static{
try {
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//System.setProperty("jdbc.drivers", "sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //虽不会对具体类产生依赖，但注册不太方便，所以较少使用
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}

public Connection getConnection() throws SQLException{
return DriverManager.getConnection(url);
}

public static void free(ResultSet rs, Statement st, Connection conn){
try {
if (rs != null)
rs.close();
} catch(SQLException e){
e.printStackTrace();
}finally {
try {
if(st != null)
st.close();
} catch(SQLException e){
e.printStackTrace();
} finally {
if(conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

}
}
}

-----------------------------

package cn.itcast.jdbc;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
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 cn.itcast.jdbc.domain.User;

public class ORMTest {

/**
* @param args
* @throws InvocationTargetException
* @throws IllegalAccessException
* @throws SQLException
* @throws IllegalArgumentException
* @throws InstantiationException
*/
public static void main(String[] args) throws IllegalArgumentException,
SQLException, IllegalAccessException, InvocationTargetException, InstantiationException {
User user = (User)getObject("select id as Id, name as Name, birthday as Birthday, money as Money from 用户 where id=1", User.class);
System.out.println(user);
}

static Object getObject(String sql, Class clazz) throws SQLException,
IllegalArgumentException, IllegalAccessException,
InvocationTargetException, InstantiationException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
String[] colNames = new String[count];

for (int i = 1; i <= count; i++) {
colNames[i - 1] = rsmd.getColumnLabel(i);
}
Object obj = null;
Method[] ms = clazz.getMethods();

if (rs.next()) {
obj = clazz.newInstance();
for (int i = 0; i < colNames.length; i++) {
String colName = colNames[i];
String methodName = "set" + colName;
System.out.println(methodName);
for (Method m : ms) {
if (methodName.equals(m.getName())) {
m.invoke(obj, rs.getObject(colName));
}
}
}
}
return obj;
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}

-------------------------------------

package cn.itcast.jdbc;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.imageio.stream.FileImageInputStream;

public class BlobTest {

/**
* @param args
* @throws SQLException
* @throws IOException
*/
public static void main(String[] args) throws IOException, SQLException {
//create();
read();
}

static void create() throws IOException, SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
conn = JdbcUtils.getConnection();
/*String sql = "insert into 班级信息(班级编号,班级名,班级人数,所属系别,辅导员 )" +
"values('10-1', 'software', 37, 'COMPUTER', '陈英慧')";*/
String sql = "insert into blob_test(big_bit)values(?)" ;
ps = conn.prepareStatement(sql);
File file = new File("background.jpg");
InputStream is = new BufferedInputStream(new FileInputStream(file));
//ps.setBinaryStream(1, is);
ps.setBinaryStream(1, is, file.length());

int i = ps.executeUpdate();
is.close();
System.out.println("i=" + i);
}finally{
JdbcUtils.free(rs, ps, conn);
}
}

static void read() throws SQLException, IOException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //比单例简单些
//conn = JdbcUtilsSingle.getInstance().getConnection(); //通过单例
st = conn.createStatement();
rs = st.executeQuery("select big_bit from blob_test");
while(rs.next()){
/*Blob blob = rs.getBlob(1);
InputStream is = blob.getBinaryStream();*/
InputStream is = rs.getBinaryStream(1);
File file = new File("background_bak.jpg");
OutputStream os = new BufferedOutputStream(new FileOutputStream(file));
byte[] buff = new byte[1024];
for(int i = 0; (i = is.read(buff)) > 0;){
os.write(buff, 0, i);
}
is.close();
os.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
}

------------------------------------

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CRUD {

/**
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
//create();
read();
}

static void/* create()*/read() /*update() *//*delete() */throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //比单例简单些
//conn = JdbcUtilsSingle.getInstance().getConnection(); //通过单例
st = conn.createStatement();
rs = st.executeQuery("select Login_Name,Login_Psw from T_register");
while(rs.next()){
System.out.println(rs.getString("Login_Name") + "\t" + rs.getObject("Login_Psw"));
}
//-------------------数据插入----------------------------
//String sql = "insert into fan(Login_Name, Login_Psw) values ('张衡', '123456')";
//int i = st.executeUpdate(sql);
//System.out.println("i=" + i);
//----------------------------------------------
//-------------------数据更新----------------------------
//String sql = "update fan set Login_Psw=1234567";
//int i = st.executeUpdate(sql);
//System.out.println("i=" + i);
//----------------------------------------------
} finally {
JdbcUtils.free(rs, st, conn);
}
}
}

---------------------------------------

package cn.itcast.jdbc;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ClobTest{
public static void main(String[] args) throws IOException, SQLException{
//create();
read();
}

static void create() throws IOException, SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
conn = JdbcUtils.getConnection();
/*String sql = "insert into 班级信息(班级编号,班级名,班级人数,所属系别,辅导员 )" +
"values('10-1', 'software', 37, 'COMPUTER', '陈英慧')";*/
String sql = "insert into clob_test(big_text)values(?)" ;
ps = conn.prepareStatement(sql);
File file = new File("src/cn/itcast/jdbc/JdbcUtils.java");
Reader reader = new BufferedReader(new FileReader(file));
ps.setCharacterStream(1, reader, file.length());

int i = ps.executeUpdate();
reader.close();
System.out.println("i=" + i);
}finally{
JdbcUtils.free(rs, ps, conn);
}
}

static void read() throws SQLException, IOException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //比单例简单些
//conn = JdbcUtilsSingle.getInstance().getConnection(); //通过单例
st = conn.createStatement();
rs = st.executeQuery("select big_text from clob_test");
while(rs.next()){
Clob clob = rs.getClob(1);
Reader reader = clob.getCharacterStream();
//reader = rs.getCharacterStream(1);
File file = new File("JdbcUtils_bak.java");
Writer writer = new BufferedWriter(new FileWriter(file));
char[] buff = new char[1024];
for(int i = 0; (i = reader.read(buff)) > 0;){
writer.write(buff, 0, i);
}
reader.close();
writer.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
}

-------------------------------------------------

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ParemeterMetaTest {

public static void main(String[] args) throws SQLException {
Object[] params = new Object[] { "小五",
new Date(System.currentTimeMillis()), 1000f };
read(
"select id,name,birthday,money from 用户 where name=? and birthday<? and money>?",
params);
}

static void read(String sql, Object[] params) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); // 比单例简单些
ps = conn.prepareStatement(sql);
ParameterMetaData pmd = ps.getParameterMetaData();
int count = pmd.getParameterCount();
for (int i = 1; i <= count; i++) {
// System.out.print(pmd.getParameterClassName(i) + "\t");
// System.out.print(pmd.getParameterType(i) + "\t");
// System.out.println(pmd.getParameterTypeName(i));
ps.setObject(i, params[i - 1]);
}

rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t"
+ rs.getString("name") + "\t" + rs.getDate("birthday")
+ "\t" + rs.getFloat("money"));
}

} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}

---------------------------------------------------------

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

public class SavePointTest {

/**
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
test();
}

static void test() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
Savepoint sp = null;
try {
conn = JdbcUtils.getConnection(); //比单例简单些
//conn = JdbcUtilsSingle.getInstance().getConnection(); //通过单例
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
st = conn.createStatement();
String sql = "update 用户 set money=money-100 where id=2";
st.executeUpdate(sql);
sp = conn.setSavepoint();

sql = "update 用户 set money=money+1000 where id=4";
st.executeUpdate(sql);

sql = "select money from 用户 where id=3";
rs = st.executeQuery(sql);
float money = 0.0f;
if(rs.next()){
money=rs.getFloat("money");
}
if(money > 2000)
throw new RuntimeException("已经超过最大值");
sql = "update 用户 set money=money+100 where id=3";
st.executeUpdate(sql);
conn.commit();
} catch(RuntimeException e) {
if(conn != null && sp != null){
conn.rollback(sp);
conn.commit();
}
throw e;
} catch(SQLException e){
if(conn != null)
conn.rollback();
throw e;
}finally {
JdbcUtils.free(rs, st, conn);
}
}

}

-------------------------------------------------

package cn.itcast.jdbc;

import java.sql.Connection;
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;

public class ResultSetMetaDataTest {

public static void main(String[] args) throws SQLException {
List<Map<String, Object>> datas = read("select id,name from 用户 where id<4");
System.out.println(datas);
}

static List<Map<String, Object>> read(String sql) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try{
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
String[] colNames = new String[count];

for(int i = 1; i<=count; i++){
/*System.out.print(rsmd.getColumnClassName(i) + "\t");
System.out.print(rsmd.getColumnName(i) + "\t");
System.out.println(rsmd.getColumnLabel(i));*/
colNames[i-1] = rsmd.getColumnName(i);
}
List<Map<String, Object>> datas = new ArrayList<Map<String,Object>>();

while (rs.next()) {
Map<String, Object> data = new HashMap<String, Object>();
for(int i = 0; i < colNames.length; i++){
data.put(colNames[i], rs.getObject(colNames[i]));
}
datas.add(data);
}
return datas;
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}

---------------------------------------CRUD-----------------------------------------

package edu.sdkd.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import edu.sdkd.dao.CircleCardDao;
import edu.sdkd.dao.DaoException;
import edu.sdkd.datasource.MyDataSource;
import edu.sdkd.domain.Card;
import edu.sdkd.domain.CircleCard;

public class CircleCardDaoImpl implements CircleCardDao {

public List<CircleCard> list() {
MyDataSource dataSource = MyDataSource.getMyDataSource();
List listCircleCard = new ArrayList<CircleCard>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
CircleCard circlecard=new CircleCard();
String sql = "select * from circle_cards;";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
circlecard = mappingCircleCard(rs);
listCircleCard.add(circlecard);
System.out.println(10);
}
} catch (SQLException e) {
throw new DaoException("获取circlecard列表失败1", e);
} finally {
dataSource.free(conn);
}
return listCircleCard;
}

private CircleCard mappingCircleCard(ResultSet rs) throws SQLException {
CircleCard circleCard = new CircleCard();
circleCard.setCircleId(rs.getInt("circle"));
circleCard.setCardId(rs.getInt("cards"));
return circleCard;
}

public List<Integer> getCardId(int circleId) {
MyDataSource dataSource = MyDataSource.getMyDataSource();
List<Integer> listCardId = new ArrayList<Integer>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
String sql = "select cards from circle_cards where circle=?;";
ps = conn.prepareStatement(sql);
System.out.println("18");
ps.setInt(1, circleId);
rs = ps.executeQuery();
while (rs.next()) {
int cardId = rs.getInt("cards");
listCardId.add(cardId);
}
System.out.println("20");
} catch (SQLException e) {
throw new DaoException("获取circlecard列表失败2", e);
} finally {
dataSource.free(conn);
}
System.out.println("21");
return listCardId;
}

public List<Integer> getCircleId(int cardId) {
MyDataSource dataSource = MyDataSource.getMyDataSource();
List<Integer> listCardId = new ArrayList<Integer>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
String sql = "select circle from circle_cards where cards=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1, cardId);
rs = ps.executeQuery();
while (rs.next()) {
int circleId = rs.getInt("circle");
listCardId.add(circleId);
}
} catch (SQLException e) {
throw new DaoException("获取circlecard列表失败2", e);
} finally {
dataSource.free(conn);
}
return listCardId;
}

//把一个card放到circle
public void addCard2Circle(int cardId, int circleId) {
MyDataSource dataSource = MyDataSource.getMyDataSource();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
CircleCard circlecard=new CircleCard();
String sql = "insert into circle_cards(circle,cards) values(?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, circleId);
ps.setInt(2, cardId);
ps.executeUpdate();
} catch (SQLException e) {
throw new DaoException("把card增添到一个circle里失败", e);
} finally {
dataSource.free(conn);
}
}

public void deleteCard4Circle(int cardId, int circleId) {
MyDataSource dataSource = MyDataSource.getMyDataSource();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
CircleCard circlecard=new CircleCard();
String sql = "delete from circle_cards where circle=? and cards=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, circleId);
ps.setInt(2, cardId);
ps.executeUpdate();
} catch (SQLException e) {
throw new DaoException("把card从一个circle删除失败", e);
} finally {
dataSource.free(conn);
}
}

}