JDBC使用技术及事例

jdbc----->java data base connectivity(数据库连接)

--通过接口来取得连接驱动,api(java.sql;javax.sql)mysql-driver:(MySql-connector-java-version-bin.jar)

简单事例:

create database test Character set utf8collate utf8_general_ci;

use test

create table user

(

id int primary Key,

name varchar(20),

password varchar(20),

email varchar(20),

birthday Date

);

insert into user(id,name,password,email,birthday) values(1,'a','a1','a@sina.com','1900-1-1');

junitTest---->

public class junitTest{

public static void main(String[] args) throws Exception{

String url="jdbc:mysql://localhost:3306/test_jdbc";

String usename="root";

String password="123456";

try{

Class.forName("com.mysql.jdbc.Driver");

Connection conn=DriverManager.getConnection(url,username,password);

Statement stm=conn.createStatement();

String sql="select id,name,password,email,birthday from user";

ResultSet rs=stm.executeQuery(sql);

while(rs.next()){

int id=(Integer)rs.getObject("id");

String name=(String)rs.getObject("name");

String password=(String)rs.getObject("password");

......

}

finally{

if(rs!=null){

try{

rs.close();

}catch(Exception e){

e.printStackTrace();

}

rs=null;

}

if(stm!=null){

try{

stm.close();

}catch(Exception e){

e.printStackTrace();

}

stm=null;

}

if(conn!=null){

try{

conn.close();

}catch(Exception e){

e.printStackTrace();

}

conn=null;

}

JDBC 常用API---->

createStatement();

prepareStatement(sql);

prepareCall(sql);

setAutoCommit();

callbakc();

commit();

Statement常用方法-->

executeQuery(sql)

executeUpdate(sql)

execute();

addBatch();

executeBatch();

ResultSet使用方法-->

rs.getObject(String columnName/int index);

rs.getString(String columnName/int index);

rs.next()-->true/false;

包装JDBC---->JDBCUtils

core-code:

private static String driver=null;

private static String url=null;

private static String username=null;

private static String password=null;

InputStream is=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

Property pro=new Property();

pro.load(is);

driver=pro.getProperty("driver");

url=pro.getProperty("url");

username=pro.getProperty("username");

password=pro.getProperty("password");

Class.forName(driver);

public static Connection getConn() throws Exception{

return DriverManager.getConnection(url,username,password);

}

public static void releaseResources(Connection conn,Statement st,ResultSet rs){

if(rs!=null){

try{

rs.close();

}catch(Exception e){

e.printStackTrace();

}

rs=null;

}...

db.properties--->driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/jdbcUtils

username=root

password=123456

使用JDBCUtils实现CRUD例子数据库定义语言(DDL-->insert,update,delete DML(databas 操作语言)-->query)

public void testCreate() throws Exception{

Connection conn=JDBCUtils.getConn();

Statement st=conn.createStatement();

insertMethod-->

PrepareStatmen pst=conn.prepareStatement(sql);

String sql="insert into user(id,name,email) values(2,'sand','sand@qq.com')";

int rsCount=st.executeUpdate(sql);

if(rsCount>0){

System.out.println("insert success");

}else{

System.out.println("occur error");

}

deleteMethod-->

String sql="delete from user where id=2";

st.executeUpdate(sql);...

updateMehod-->

String sql="update use set name='king' where id=1";

st.executeUpdate(sql);

queryMehod-->

String sql=''select id,name,email from user";

ResutSet rs=st.executeQuery(sql);

while(rs.next()){

int id=(Integer)rs.getObject("id");....

}

Mysql 处理大数据类型--->

String path=Demo.class.getClassLoader().getResource("a.txt").getPath();

File file=new File(path);

Reader reader=new FileReader(file);//获取大文件的路径

create database bigdata_clob

user bigdata_clob

create table bigtext(

id int primary key,

resume text

);

public class TestClob{

Connection conn=null;

PreparedStatement pstm=null;

ResultSet rs=null;

public void testAdd(){

conn=JDBCUtils.getConnection();

String sql="insert into bigtext(resume) values(?)";

pstm=conn.preparedStatement(sql);

String path=TestClob.class.getClassLoader().getResource("textClob.txt").getPath();

File f=new File(path);

pstm.setCharacterStream(1,new FileReader(f),f.length());

int num=pstm.executeUpdate();

if(num>0){

System.out.println("add ok");

}

}

public void testRead(){

conn=JDBCUtils.getConnection();

String sql="select resume from bigText where id=?";

pstm=conn.preparedStatement(sql);

pstm.setInt(1,1);

rs=pstm.executeQuery();

if(rs.next()){

Reader reader=rs.setCharacterStream("resume");

char[] buffer=new char[1024];

int len=0;

FileWriter fw=new FileWriter("D:\\clob.txt");

while((len=reader.read(buffer))>0){

fw.writer(buffer,0,len);

}

fw.close();

reader.close();

}

}

}

处理大的图片和文本基本一样,小差别在:pstm.setBinaryStream(1,new FileInputStream(path),new File(path).length());---->add image method;

InputStream is=rs.getBinaryStream("image");---->read image method

批处理格式:preparedstatement.addBatch(sql01);pstm.addBatch(sql02)... pstm.executeBatch();

自己实现连接池(如C3P0,DBCP...)

public class JdbcPool implements DataSource{

private static LinkedList<Connection> connList=new LinkedList<Connection>();

static{

InputStream is=JdbcPool.class.getClassLoader().getResourceAsStream("db.properties");

Properties prop=new Properties();

prop.load(is);

String driver=prop.getProperty("driver");

String url=prop.getProperty("url");

String username=prop.getProperty("username");

String password=prop.getProperty("password");

Class.forName(driver);

for(int i=0;i<10;i++){

Connection conn=DriverManager.getConnection(url,username,password);

System.out.println("getConnection--->"+i);

connList.add(conn);

}

}

}

Dynamic Proxy--->

public synchronized Connection getConnection() throws SQLException{

if(connList.size()>0){

Connection connection=connList.removeFirst();

System.out.println("the pool size --->"+connList.size());

return (Connection)Proxy.newInstance(JdbcPool.class.getClassLoader(),new Class[]{Connection.class},new InvocationHandler(){

@Override

public Object invoke(Object proxy,Method method,Object[] args){

if(!method.getName().equals("close")){

return invoke(connection,args);

}else{

connList.add(connection);

System.out.println("connection return to pool"+connection);

System.out.println("connectionList size is"+connList.size());

return null;

}

}

});

}

}

dbcp用的不多现在都用开源的封装更好的C3P0

C3P0事例---->

public class DBSource_c3p0{

private static ComboPooledDataSource cpds;

static{

cpds=new ComboPooledDataSource();

}

public static Connection getConnection(){

Connection conn= cpds.getConnection();

return conn;

}

public static releaseSource(DataSource ds,Statement st,ResultSet rs){...}

}

c3p0-config.xml--->

<c3p0-config>

<default-config>

<property name="drvier">com.mysql.jdbc.Driver</property>

</default-config>

</c3p0-config>