JDBC技术总结(一)
来源:互联网 发布:淘宝来图定制步骤图片 编辑:程序博客网 时间:2024/05/22 02:26
1. JDBC简介
SUN公司为了简化、统一对数据库的操作,定义了一套Java操作数据库的规范,称之为JDBC,JDBC不能直接操作数据库,JDBC通过接口加载数据库的驱动,然后操作数据库。JDBC:Java Data Base Connectivity,它主要由接口组成。组成JDBC的2个包为java.sql和javax.sql。开发JDBC应用需要这2个包的支持外,还需要导入相应JDBC的数据库实现(即数据库驱动)。
2. JDBC快速入门
编程从user表中读取数据,并打印在控制台窗口中。
2.1 搭建实验环境
- create database test Character set utf8 collate 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,'zw','123','zw@sina.com','1990-09-19');
- insert into user(id,name,password,email,birthday) values(2,'ls','123','ls@sina.com','1991-04-19');
- insert into user(id,name,password,email,birthday) values(3,'ww','123','ww@sina.com','1992-06-19');
2.2 编写java程序
新建一个java工程,并导入数据库驱动 MySQL-connector-java-5.1.20-bin.jar。
- public class Demo1 {
-
- public static void main(String[] args) throws Exception {
-
- String url = "jdbc:mysql://localhost:3306/day14";
- String username = "root";
- String password = "root";
-
- try{
-
-
- Class.forName("com.mysql.jdbc.Driver");
-
-
- Connection conn = DriverManager.getConnection(url, username, password);
-
-
- Statement st = conn.createStatement();
-
-
- String sql = "select id,name,password,email,birthday from user";
- ResultSet rs = st.executeQuery(sql);
-
-
- while(rs.next()){
- int id = (Integer) rs.getObject("id");
- String name = (String)rs.getObject("name");
- String pd = (String)rs.getObject("password");
- String email = (String)rs.getObject("email");
- Date birthday = (Date)rs.getObject("birthday");
- System.out.println(id+","+name+","+pd+","+email+","+birthday);
- }
- }
-
- finally{
- if(rs != null){
- try{
- rs.close();
- }catch(Exception e) {
- e.printStackTrace();
- }
- rs = null;
- }
- if(st != null){
- try{
- st.close();
- }catch(Exception e) {
- e.printStackTrace();
- }
- st = null;
- }
-
- if(conn != null){
- try{
- conn.close();
- }catch(Exception e) {
- e.printStackTrace();
- }
- conn = null;
- }
- }
- }
- }
JDBC程序中的DriverManager用于加载驱动,并创建与数据库的连接,这个类的常用方法有:- DriverManager.registerDriver(new Driver());
- DriverManager.getConnection(URL, user, password);
注意:在实际开发中不推荐采用registerDriver方法注册驱动,原因有二: a. 查看Driver的源代码可以看到,如果采用此种方式,会导致驱动程序注册两次,也就是在内存中会有两个Driver对象; b. 程序依赖mysql的api,脱离mysql的jar包,程序将无法编译,将来程序切换底层数据库将会非常麻烦。 推荐方式:Class.forName("com.mysql.jdbc.Driver"); 采用此种方式不会导致驱动对象在内存中重复出现,并且采用此种方式,程序仅仅只需要一个字符串,不需要依赖具体的驱动,使程序的灵活性更高。同样,在开发中也不建议采用具体的驱动类型指向getConnection方法返回的Connection对象。3. JDBC常用API
JDBC程序中的Connection对象用于代表数据库的连接,Connection是数据库编程中最重要的一个对象,客户端与数据库所有交互都是通过Connection对象完成的。这个对象常用的方法有:
- createStatement();
- prepareStatement(sql);
- prepareCall(sql);
- setAutoCommit(boolean autoCommit);
- commit();
- rollback();
JDBC程序中的Statement对象用于向数据库发送sql语句,Statement对象常用方法有:
- <span style="font-family:Microsoft YaHei;">executeQuery(String sql);
- executeUpdate(String sql);
- execute(String sql);
- addBatch(String sql);
- executeBath();
- </span>
JDBC程序中的ResultSet对象用于代表sql语句的执行结果。ResultSet封装执行结果时,采用的类似于表格的方式。ResultSet对象维护了一个指向表格数据行的游标,初始的时候,游标在第一行之前,调用该对象的next()方法,可以使游标指向具体的数据行,进行调用方法获取该行的数据。由于ResultSet用于封装执行结果,所以该对象提供的都是用于获取数据的get方法:- <span style="font-family:Microsoft YaHei;">
- getObject(int index);
- getObject(String columnName);
-
- getString(int index);
- getString(String columnName);
- </span>
ResultSet除了提供get方法以外,还提供了对结果集进行滚动的方法:
- <span style="font-family:Microsoft YaHei;">next();
- previous();
- absolute(int row);
- beforeFirst();
- afterLast();
- </span>
为了确保资源释放代码能运行,资源释放代码一定要写在finally语句中。
4. JDBC实现增删改查
JDBC技术主要是同数据库打交道,那么免不了增删改查,由上面的代码可以看出,在对数据库进行操作之前需要先建立连接,在操作之后都需要释放资源,所以我们可以把这两部分内容抽取出来,写到JDBCUtils类中来实现:
- public class JDBCUtils {
-
- private static String driver = null;
- private static String url = null;
- private static String username = null;
- private static String password = null;
-
-
- static{
- try {
-
- InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
- Properties prop = new Properties();
- prop.load(in);
- driver = prop.getProperty("driver");
- url = prop.getProperty("url");
- username = prop.getProperty("username");
- password = prop.getProperty("password");
-
- Class.forName(driver);
-
- } catch (Exception e) {
- throw new ExceptionInInitializerError(e);
- }
- }
-
- public static Connection getConnection() throws SQLException{
-
- return DriverManager.getConnection(url, username, password);
- }
-
- public static void release(Connection conn, Statement st, ResultSet rs){
-
- if(rs != null){
- try{
- rs.close();
- }catch(Exception e) {
- e.printStackTrace();
- }
- rs = null;
- }
- if(st != null){
- try{
- st.close();
- }catch(Exception e) {
- e.printStackTrace();
- }
- st = null;
- }
-
- if(conn != null){
- try{
- conn.close();
- }catch(Exception e) {
- e.printStackTrace();
- }
- conn = null;
- }
- }
-
- }
db.properties文件:
- driver=com.mysql.jdbc.Driver
- url=jdbc:mysql://localhost:3306/day14
- username=root
- password=root
这样我们就完成了数据库的连接和资源的释放工具类。下面我们开始编写对数据库的增删改查:
-
- public class Demo2 {
-
- private Connection conn = null;
- private Statement st = null;
- private ResultSet rs = null;
-
- @Test
- public void insert(){
-
- try {
- conn = JDBCUtils.getConnection();
- st = conn.createStatement();
- String sql = "insert into user(id,name,password,email,birthday) values(3,'ww','123','ww@sina.com','1982-06-14');";
- int num = st.executeUpdate(sql);
- if(num > 0){
- System.out.println(num);
- System.out.println("插入成功");
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally {
- JDBCUtils.release(conn, st, rs);
- }
-
- }
-
- @Test
- public void delete(){
-
- try {
- conn = JDBCUtils.getConnection();
- st = conn.createStatement();
- String sql = "delete from user where password='123'";
- int num = st.executeUpdate(sql);
- if(num > 0){
- System.out.println(num);
- System.out.println("删除成功");
- }
- } catch (Exception e) {
-
- e.printStackTrace();
- }finally {
- JDBCUtils.release(conn, st, rs);
- }
-
- }
-
- @Test
- public void update(){
-
- try {
- conn = JDBCUtils.getConnection();
- st = conn.createStatement();
- String sql = "update user set password='456' where name='ww'";
- int num = st.executeUpdate(sql);
- if(num > 0) {
- System.out.println(num);
- System.out.println("修改成功");
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally {
- JDBCUtils.release(conn, st, rs);
- }
-
- }
-
- @Test
- public void find(){
-
- try {
- conn = JDBCUtils.getConnection();
- st = conn.createStatement();
- String sql = "select id,name,password,email,birthday from user";
- rs = st.executeQuery(sql);
- while(rs.next()){
- int id = (Integer)rs.getObject("id");
- String name = (String)rs.getObject("name");
- String password = (String)rs.getObject("password");
- String email = (String)rs.getObject("email");
- Date birthday = (Date)rs.getObject("birthday");
- System.out.println(id+","+name+","+password+","+email+","+birthday);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally {
- JDBCUtils.release(conn, st, rs);
- }
-
- }
-
- }
JDBC基本知识点就介绍到这,后面再介绍一些高级点的应用,如有错误之处,欢迎留言指正~
上一节主要讲了JDBC的基本操作,这一节主要总结一下JDBC如何处理大文本、如何处理图片以及进行批处理。
1.JDBC处理大文本
在MySQL中,大文本是text类型,使用Java操作数据库中的大文本需要两个方法setCharacterStream和getCharacterStream,一个是写入数据库的字符流,一个是从数据库中读取的字符流。setCharacterStream(index,Reader,length)有三个参数,Reader表示获取文件的流,length表示文件的长度,index表示参数的索引。那么获取文件的流Reader如何获得呢?我们可以通过下面方法获取:
- String path = Demo1.class.getClassLoader().getResource("1.txt").getPath();
- File file = new File(path);
- Reader reader = new FileReader(file);
下面我们来看一下如何向数据库中存入大文本以及如何从数据库中取出大文本。先建立一个数据库:
- create database tes<span style="font-family:Microsoft YaHei;">t</span>;
- use test;
- create table testclob
- (
- id int primary key auto_increment,
- resume text
- );
下面看Demo1.java:
- public class Demo1 {
-
- @Test
- public void add() throws FileNotFoundException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
-
- try {
- conn = JDBCUtils.getConnection();
- String sql = "insert into testclob(resume) values(?)";
- st = conn.prepareStatement(sql);
-
- String path = Demo1.class.getClassLoader().getResource("1.txt").getPath();
- File file = new File(path);
- st.setCharacterStream(1, new FileReader(file), file.length());
- int num = st.executeUpdate();
- if(num > 0) {
- System.out.println("插入成功");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
-
- @Test
- public void read() throws IOException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
- try {
- conn = JDBCUtils.getConnection();
- String sql = "select resume from testclob where id=?";
- st = conn.prepareStatement(sql);
- st.setInt(1, 1);
- rs = st.executeQuery();
- if(rs.next()){
- Reader reader = rs.getCharacterStream("resume");
- char buffer[] = new char[1024];
- int len = 0;
- FileWriter out = new FileWriter("D:\\1.txt");
- while((len = reader.read(buffer)) > 0){
- out.write(buffer, 0, len);
- }
- out.close();
- reader.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
-
- }
以上就是处理大文本的方法,比较简单,下面我们来看看如何处理图片。2. JDBC处理图片
在MySQL中,大文本是image类型,我们现在数据库中建立新的表:
- create table testblob
- (
- id int primary key auto_increment,
- image longblob
- );
然后我们直接看处理图片的java程序Demo2.java:- public class Demo2 {
- @Test
- public void add() throws FileNotFoundException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
-
- try {
- conn = JdbcUtils.getConnection();
- String sql = "insert into testblob(image) values(?)";
- st = conn.prepareStatement(sql);
- String path = Demo2.class.getClassLoader().getResource("honey.jpg").getPath();
- st.setBinaryStream(1, new FileInputStream(path), new File(path).length());
-
-
- int num = st.executeUpdate();
-
-
-
-
-
-
- if(num > 0){
- System.out.println("插入成功");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
-
- @Test
- public void read() throws IOException {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
-
- try {
- conn = JdbcUtils.getConnection();
- String sql = "select image from testblob where id=?";
- st = conn.prepareStatement(sql);
- st.setInt(1, 1);
- rs = st.executeQuery();
- while(rs.next()){
- InputStream in = rs.getBinaryStream("image");
- int len = 0;
- byte buffer[] = new byte[1024];
- FileOutputStream out = new FileOutputStream("D:\\honey.jpg");
- while((len = in.read(buffer)) > 0){
- out.write(buffer, 0, len);
- }
- out.close();
- in.close();
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
-
- }
JDBC操作图片也比较简单,跟操作大文本差不多。下面看看JDBC如何进行批处理。
3. JDBC进行批处理
首先我们还是先建立一个表:
- create table testbatch
- (
- id int primary key,
- name varchar(20)
- );
然后我们看看JDBC进行批处理的具体代码:
-
- public class Demo3 {
-
- @Test
- public void testBatch() {
- Connection conn = null;
- Statement st = null;
- ResultSet rs = null;
-
- try {
- conn = JdbcUtils.getConnection();
- String sql1 = "insert into testbatch(id,name) values(1,'aaa')";
- String sql2 = "insert into testbatch(id,name) values(2,'bbb')";
- String sql3 = "insert into testbatch(id,name) values(3,'ccc')";
- String sql4 = "delete from testbatch where id=1";
-
- st = conn.createStatement();
- st.addBatch(sql1);
- st.addBatch(sql2);
- st.addBatch(sql3);
- st.addBatch(sql4);
-
- st.executeBatch();
- st.clearBatch();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
-
- @Test
- public void testBatch2() {
- Connection conn = null;
- PreparedStatement st = null;
- ResultSet rs = null;
-
- try {
- conn = J<span style="font-family:Microsoft YaHei;">DBC</span>Utils.getConnection();
- String sql = "insert into testbatch(id,name) values(?,?)";
- st = conn.prepareStatement(sql);
-
- for(int i = 0; i < 10000000; i++){
- st.setInt(1, i);
- st.setString(2, "aa"+i);
- st.addBatch();
- if(i % 1000 == 0){
- st.executeBatch();
- st.clearBatch();
- }
- }
- st.executeBatch();
- st.clearBatch();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- JdbcUtils.release(conn, st, rs);
- }
- }
- }
JDBC大文本、图片的操作以及批处理其实很简单,掌握几个关键的API就可以了,不再赘述,如有错误之处,欢迎留言指正~ 0 0