什么是JDBC？为什么使用JDBC
1)SUN公司为使Java程序灵活的访问各种不同的关系型数据库提供的规则。 2)为什么使用JDBC？ a)降低Java程序操作数据库的成本 b)使Java程序灵活移植 使用的是接口的思想 不同数据库的访问方式，也就是类大都是不同的，这样子当你要操作一种数据库的时候就要去学一种相对应的规则，这是很不科学的， 解决方法就是通过接口。所以sum公司就给出了方法然后让各个开发数据库公司实现这些方法，这样子程序员就是使用一套规则操作所有数据库，这个就是所谓的JDBC，也就是javadatebaseconnectitify
关闭流的顺序
a)依次按resul->statement->connection顺序关闭 b)在能完成业务的情况下，尽早关闭、result，statement：轻量级，创建和销毁rs所需要的时间和资源较小。
connection：重量级，上面要关闭的两个都是通过这个来的，如果实在不行，其他可以不管，这个一定要关闭。
JDBC的六个固定步骤
1，注册数据库驱动[利用反射] 2，取得数据库连接对象Connection 3，创建SQL对象 4，执行SQL命令，并返回结果集 5，处理结果集 6，依次关闭结果集
基于JDBC的CURD操作
属性配置文件
driver = com.mysql.jdbc.Driverurl = jdbc:mysql://127.0.0.1:3306/mydb2user = rootpassword =rootJDBC工具类
package cn.itcast.web.jdbc;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.Properties;//JDBC工具类：关闭流和取得连接public final class JdbcUtil { private static String driver; private static String url; private static String user; private static String password; //静态块：加载文件 static{ Properties props = new Properties(); InputStream is = Crud.class.getClassLoader().getResourceAsStream("cn/itcast/web/jdbc/db.properties"); //其实也可以不用demo3，而用this，但是因为这里是static，所以就没有this try { props.load(is); } catch (Exception e) { e.printStackTrace(); } driver = props.getProperty("driver"); url = props.getProperty("url"); user = props.getProperty("user"); password = props.getProperty("password"); } //静态块：注册驱动 static{ try { Class.forName(driver); } catch (Exception e) { e.printStackTrace(); } } //取得连接 public static Connection getMySqlConnection(){ Connection conn = null; try { conn = DriverManager.getConnection(url,user,password); } catch (Exception e) { e.printStackTrace(); } return conn; } //关闭连接 public static void close(ResultSet rs){ if(rs!=null){ try { rs.close(); } catch (Exception e) { e.printStackTrace(); } } } public static void close(Statement stmt){ if(stmt!=null){ try { stmt.close(); } catch (Exception e) { e.printStackTrace(); } } } public static void close(Connection conn){ if(conn!=null){ try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } }} CRUD操作
package cn.itcast.web.jdbc;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import org.junit.Test;//基于JDBC的CURD操作public class Crud { @Test public void create(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "insert into user(name,gender,salary) values('丝丝','女',3000)"; try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); int i = stmt.executeUpdate(sql); //返回的是印象的行数 System.out.println(i>0?"成功":"失败"); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } @Test public void read(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "select * from user where gender='male'"; try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()){ String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(name+":"+gender); } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } @Test public void update(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "update user set gender='female' where id = 8"; try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); int i = stmt.executeUpdate(sql); //返回的是影响到的行数，也就是有多少记录 System.out.println(i>0?"成功":"失败"); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } @Test public void delete(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "delete from user where salary IS NULL"; try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); int i = stmt.executeUpdate(sql); System.out.println(i>0?"成功":"失败"); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } @Test public void read(String name){ Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "select * from user where name='"+name+"'"; System.out.println("sql="+sql); try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()){ //while(rs!=null) name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(name+":"+gender); } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } public void createTable(String tableName){ } public void dropTable(String tableName){ } public static void main(String[] args) { Crud crud = new Crud(); //crud.read(" 'or true or' "); crud.createTable("system"); crud.dropTable("system"); }}
内容补充
PreparedStatement有发下的特点：
Statement没有任何的检测功能，PreparedStatement这个子类就有 预编译作用，这样就可以对数据进行检测
>>解决SQL注入问题，在绑定参数时，动态检测 >>在发送相同结构的SQL时，较Statement效率有所提升 Statement如果执行100次insert语句，就要判断100次数据类型，而PreparedStatement只需要预编译第一次，就相对来说有效率 >>使用？占位符替代真实的绑定值 >>项目中，优先使用PreparedStatement
批处理
1)想发送多条SQL，又要降低与数据库系统的交互，这时使用批处理
2)Statement对象：适合对不同结构的SQL做批处理操作 对于相同SQL结构的，Statement只是减少了发送数据的次数，但是没有减少编译的次数，PreparedStatement就能实现只编译一次 3)PreparedStatement对象：适合对相同结构的SQL做批处理操作
package cn.itcast.web.jdbc.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import cn.itcast.web.jdbc.util.JdbcUtil;//Statement和PreparedStatment的批处理public class Demo3 { public static void statementBatch() { Connection conn = null; Statement stmt = null; ResultSet rs = null; String insertSQL = "insert into user(username,password,birthday,salary) values('jack','000111','2011-10-26',5000)"; String updateSQL = "update user set username='杰克' where username='jack'"; try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); //将需要执行的多条命令加入到批对象中 stmt.addBatch(insertSQL); stmt.addBatch(updateSQL); //一次性发送批对象到数据库端执行,返回每条SQL的结果 int[] is = stmt.executeBatch(); //将批对象清空 stmt.clearBatch(); //显示结果 System.out.println(is[0]+":"+is[1]); //1:1 返回的是改变多少记录 } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } public static void preparedBatch() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String insertSQL = "insert into user(username,password,birthday,salary) values(?,?,?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(insertSQL); long begin = System.currentTimeMillis(); for(int i=1;i<=1000;i++){ pstmt.setString(1,"jack"+i); pstmt.setString(2,"111111"); pstmt.setDate(3,new java.sql.Date(12345)); pstmt.setFloat(4,5000); //加入到批对象中 pstmt.addBatch(); if(i%100==0){ //如果数据比较大的话，可以控制多少条的时候执行一次，不要到最后一大堆数据一起执行 //执行批对象 pstmt.executeBatch(); //清空批对象 pstmt.clearBatch(); } } //执行批对象 pstmt.executeBatch(); //清空批对象 pstmt.clearBatch(); long end = System.currentTimeMillis(); System.out.println((end-begin)/1000+"秒"); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public static void main(String[] args) { //statementBatch(); preparedBatch(); }} 获取数据库主键值 1)当需要获取刚插入主键信息的时候，需要使用获取主键值方法 2)关键代码： pstmt = conn.prepareStatement(sqlA,Statement.RETURN_GENERATED_KEYS); rs = pstmt.getGeneratedKeys(); if(rs.next()){ Long temp = (Long) rs.getObject(1); pid = temp.intValue(); } package cn.itcast.web.jdbc.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import cn.itcast.web.jdbc.util.JdbcUtil;//获取数据库表插入的主键public class Demo4 { public static void main(String[] args) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String insertSQL = "insert into user(username,password,birthday,salary) values('jack','000111','2011-10-26',5000)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(insertSQL,Statement.RETURN_GENERATED_KEYS); pstmt.executeUpdate(); //获取插入记录的键值 rs = pstmt.getGeneratedKeys(); if(rs.next()){ Long temp = (Long) rs.getObject(1); int primaryValue = temp.intValue(); System.out.println("主键值="+primaryValue); } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } }} 字符流的形式读取或者插入数据库大数据文本。
package cn.itcast.web.jdbc.dao;import java.io.File;import java.io.FileReader;import java.io.FileWriter;import java.io.IOException;import java.io.Reader;import java.io.Writer;import java.net.URL;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.UUID;import cn.itcast.web.jdbc.util.JdbcUtil;public class Demo1 { //将CLOB类型的数据从MySQL数据库取出，放到d:\62.txt public static void read() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from test_clob"; Reader reader = null; Writer writer = null; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ reader = rs.getCharacterStream("content"); } } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } try { writer = new FileWriter("d:\\62.txt"); int len = 0; char[] cuf = new char[1024]; while( (len=reader.read(cuf))>0 ){ writer.write(cuf,0,len); } } catch (Exception e) { }finally{ if(reader!=null){ try { reader.close(); } catch (IOException e) { e.printStackTrace(); } } if(writer!=null){ try { writer.close(); } catch (IOException e) { e.printStackTrace(); } } } } //写CLOB类型的数据存入MySQL数据库 public static void write() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into test_clob(id,content) values(?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,UUID.randomUUID().toString()); URL url = Demo1.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/62.txt"); File file = new File(url.getPath()); Reader reader = new FileReader(file); pstmt.setCharacterStream(2,reader,(int)file.length()); int i = pstmt.executeUpdate(); System.out.println(i>0?"成功":"失败"); } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public static void main(String[] args) { //write(); read(); }} 字节流的形式读取或者插入数据库大数据文本。
package cn.itcast.web.jdbc.dao;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.net.URL;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.UUID;import cn.itcast.web.jdbc.util.JdbcUtil;/*drop table if exists test_blob;create table test_blob( id varchar(40) primary key, content mediumblob); */public class Demo2 { public boolean write(String photoPath) { boolean flag = false; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into test_blob(id,content) values(?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,UUID.randomUUID().toString()); File file = new File(photoPath); InputStream is = new FileInputStream(file); pstmt.setBinaryStream(2,is,(int)file.length()); int i = pstmt.executeUpdate(); if(i>0){ flag = true; } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } return flag; } //将BLOB类型数据存入数据库 public static void write() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into test_blob(id,content) values(?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); //绑定ID pstmt.setString(1,UUID.randomUUID().toString()); //取得图片的路径 URL url = Demo2.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/d1.jpg"); //封装成File对象 File file = new File(url.getPath()); //取得字节输入流 InputStream is = new FileInputStream(file); //绑定CONTENT //参数1占位符的编号，从1开始 //参数2文件字节输入流 //参数3文件的大小 pstmt.setBinaryStream(2,is,(int)file.length()); int i = pstmt.executeUpdate(); System.out.println(i>0?"成功":"失败"); } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } //将BLOB类型数据从数据库中取出 public static void read() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from test_blob"; InputStream is = null; OutputStream os = null; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ is = rs.getBinaryStream("content"); } } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } try { os = new FileOutputStream("d:\\d1.jpg"); int len = 0; byte[] buf = new byte[1024]; while( (len=is.read(buf))>0 ){ os.write(buf,0,len); } } catch (Exception e) { }finally{ if(is!=null){ try { is.close(); } catch (IOException e) { e.printStackTrace(); } } if(os!=null){ try { os.close(); } catch (IOException e) { e.printStackTrace(); } } } } public static void main(String[] args) { //write(); read(); }}