JDBC的基本用法-2

来源：互联网发布：net snmp linux 下载编辑：程序博客网时间：2024/05/22 08:24

一、批处理（Batch）

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 测试批处理,对于大量的批处理，建议使用Statement，因为PreparedStatement的预编译空间有限，当数据特别大时，会出现异常 * @author Administrator * */public class Demo05 {    public static void main(String[] args) {        Connection con = null;        Statement stmt = null;        ResultSet rs = null;        try {            Class.forName("com.mysql.jdbc.Driver");            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","529457099");            long start = System.currentTimeMillis();            con.setAutoCommit(false);  //设为手动提交，JDBC中默认为true，自动提交事务            stmt = con.createStatement();            for(int i=0;i<20000;i++){                //这样就可以用很短的时间处理数据                stmt.addBatch("insert into t_user (username,pwd,regTime) values ('gao"+i+"',6666,now())");            }            stmt.executeBatch();            con.commit();  //提交事务            long end = System.currentTimeMillis();            System.out.println(end-start);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }finally{            try {                if(con!=null){                    rs.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if(con!=null){                    stmt.close();  //后开的先关                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if(con!=null){                    con.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

二、事务
这里写图片描述

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/** * 测试事物的基本概念和用法 * @author Administrator * */public class Demo06 {    public static void main(String[] args) {        Connection con = null;        PreparedStatement ps1 = null;        PreparedStatement ps2 = null;        try {            Class.forName("com.mysql.jdbc.Driver");            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","529457099");            con.setAutoCommit(false); //JDBC中默认为true，自动提交事务            String sql = "insert into t_user (username,pwd) values (?,?)";  //问号叫做占位符，这样可以避免SQL注入,*表示所有的列数            ps1 = con.prepareStatement(sql);            ps1.setObject(1, "fengzi");            ps1.setObject(2, "7351");            ps1.execute();            try {                Thread.sleep(2000);            } catch (InterruptedException e) {                e.printStackTrace();            }            ps2 = con.prepareStatement(sql);            ps2.setObject(1, "fengye");            ps2.setObject(2, "56546");            ps2.execute();            con.commit();        } catch (ClassNotFoundException e) {            e.printStackTrace();            try {                con.rollback();            } catch (SQLException e1) {                e1.printStackTrace();            }        } catch (SQLException e) {            e.printStackTrace();        }finally{            try {                if(con!=null){                    ps1.close();  //后开的先关                    ps2.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if(con!=null){                    con.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

三、时间类型的操作
这里写图片描述

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Timestamp;import java.util.Random;/** * 测试时间处理(java.sql.Date,Time,Timestamp) */public class Demo07 {    public static void main(String[] args) {        Connection conn = null;        PreparedStatement ps = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","mysql");            for (int i = 0; i < 1000; i++) {                ps = conn.prepareStatement("insert into t_user(userName,pwd,regTime,lastLoginTime)values(?,?,?,?)");                ps.setObject(1, "小高" + i);                ps.setObject(2, "123");                //                int random = 1000000000 + new Random().nextInt(1000000000); //随机时间                java.sql.Date date = new java.sql.Date(System.currentTimeMillis() - random);    //插入随机时间                java.sql.Timestamp stamp = new Timestamp(System.currentTimeMillis());   //如果需要插入指定时间，可以使用Calendar、DateFormat                ps.setDate(3, date);                ps.setTimestamp(4, stamp);                //                ps.execute();            }            System.out.println("插入");        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        } finally{            try {                if (ps!=null) {                    ps.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (conn!=null) {                    conn.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

时间操作、时间段和日期段查询

import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.text.DateFormat;import java.text.ParseException;import java.text.SimpleDateFormat;/** * 测试时间处理(java.sql.Date,Time,Timestamp)，取出指定时间段的数据 */public class Demo08 {    /**     * 将字符串代表的时间转为long数字(格式：yyyy-MM-dd hh:mm:ss)     * @param dateStr     * @return     */    public static long str2DateTime(String dateStr){        DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");        try {            return format.parse(dateStr).getTime();        } catch (ParseException e) {            e.printStackTrace();            return 0;        }    }    public static void main(String[] args) {        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","mysql");            //            ps = conn.prepareStatement("select * from t_user where regTime > ? and regTime < ?");            java.sql.Date start = new java.sql.Date(str2DateTime("2016-06-20 00:00:00"));            java.sql.Date end = new java.sql.Date(str2DateTime("2016-06-24 00:00:00"));            ps.setObject(1, start);            ps.setObject(2, end);            rs = ps.executeQuery();            while(rs.next()){                System.out.println(rs.getInt("id") + "--" + rs.getString("userName")+"--"+rs.getDate("regTime"));            }            //        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        } finally{            try {                if (ps!=null) {                    ps.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (conn!=null) {                    conn.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

四、CLOB文本大对象操作
这里写图片描述

import java.io.BufferedReader;import java.io.ByteArrayInputStream;import java.io.File;import java.io.FileReader;import java.io.InputStreamReader;import java.io.Reader;import java.sql.Clob;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/** * 测试CLOB   文本大对象的使用 * 包含：将字符串、文件内容插入数据库中的CLOB字段和将CLOB字段值取出来的操作。 */public class Demo09 {    public static void main(String[] args) {        Connection conn = null;        PreparedStatement ps = null;        PreparedStatement ps2 = null;        ResultSet rs = null;        Reader r = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","mysql");            //插入//            ps = conn.prepareStatement("insert into t_user(userName,myInfo)values(?,?)");            ps.setString(1, "小高");            //将文本文件内容直接输入到数据库中//          ps.setClob(2, new FileReader(new File("G:/JAVA/test/a.txt")));            //将程序中的字符串输入到数据库中的CLOB字段中            ps.setClob(2, new BufferedReader(new InputStreamReader(new ByteArrayInputStream("aaaa".getBytes()))));            ps.executeUpdate();            System.out.println("插入");            //            //查询//            ps2 = conn.prepareStatement("select * from t_user where id=?");            ps2.setObject(1, 223021);            rs = ps2.executeQuery();            System.out.println("查询");            while (rs.next()) {                Clob c = rs.getClob("myInfo");                r = c.getCharacterStream();                int temp = 0;                while ((temp=r.read())!=-1) {                    System.out.print((char)temp);                }            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (Exception e) {            e.printStackTrace();        } finally{            try {                if (r!=null) {                    r.close();                }            } catch (Exception e) {                e.printStackTrace();            }            try {                if (rs!=null) {                    rs.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (ps2!=null) {                    ps2.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (ps!=null) {                    ps.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (conn!=null) {                    conn.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

五、BLOB-二进制大对象的使用
这里写图片描述

import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.sql.Blob;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/** * 测试BLOB   二进制大对象的使用 */public class Demo10 {    public static void main(String[] args) {        Connection conn = null;        PreparedStatement ps = null;        PreparedStatement ps2 = null;        ResultSet rs = null;        InputStream is = null;        OutputStream os = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","mysql");            //插入//            ps = conn.prepareStatement("insert into t_user(userName,headImg)values(?,?)");            ps.setString(1, "小高");            ps.setBlob(2, new FileInputStream("G:/JAVA/test/d.jpg"));            ps.execute();            //            //查询//            ps2 = conn.prepareStatement("select * from t_user where id=?");            ps2.setObject(1, 223024);            rs = ps2.executeQuery();            System.out.println("查询");            while (rs.next()) {                Blob b = rs.getBlob("headImg");                is = b.getBinaryStream();                os = new FileOutputStream("G:/JAVA/test/h.jpg");                int temp = 0;                while ((temp=is.read())!=-1) {                    os.write(temp);                }            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (Exception e) {            e.printStackTrace();        } finally{            try {                if (os!=null) {                    os.close();                }            } catch (Exception e) {                e.printStackTrace();            }            try {                if (is!=null) {                    is.close();                }            } catch (Exception e) {                e.printStackTrace();            }            try {                if (rs!=null) {                    rs.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (ps2!=null) {                    ps2.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (ps!=null) {                    ps.close();                }            } catch (SQLException e) {                e.printStackTrace();            }            try {                if (conn!=null) {                    conn.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

六、代码总结、简单封装、资源文件properties处理连接信息

#右击该properties文件--properties--Resource--Text file encoding，选中other,选择其它编码方式。#如UTF-8或GBK,这样就能在properties里面输入中文，而不会自动转成Unicode了。#java中的properties文件是一种配置文件，主要用于表达配置信息。#文件类型为*.properties，格式为文本文件，文件内容是"键=值"的格式。#在properties文件中，可以用"#"来作注释#MySQL连接配置mysqlDriver=com.mysql.jdbc.DrivermysqlURL=jdbc:mysql://localhost:3306/testjdbcmysqlUser=rootmysqlPwd=mysql#Oracle连接配置#...

import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JDBCUtil {    static Properties pros = null;  //可以帮助读取和处理资源文件中的信息    static {    //加载JDBCUtil类的时候调用        pros = new Properties();        try {            pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));        } catch (IOException e) {            e.printStackTrace();        }    }    public static Connection getMysqlConn(){        try {            Class.forName(pros.getProperty("mysqlDriver"));            return DriverManager.getConnection(pros.getProperty("mysqlURL"),                    pros.getProperty("mysqlUser"),pros.getProperty("mysqlPwd"));        } catch (Exception e) {            e.printStackTrace();            return null;        }    }    //可以重载多个，这里就懒得写了    public static void close(ResultSet rs,Statement st,Connection conn){        try {            if (rs!=null) {                rs.close();            }        } catch (SQLException e) {            e.printStackTrace();        }        try {            if (st!=null) {                st.close();            }        } catch (SQLException e) {            e.printStackTrace();        }        try {            if (conn!=null) {                conn.close();            }        } catch (SQLException e) {            e.printStackTrace();        }    }}

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;/** * 测试使用JDBCUtil工具类来简化JDBC开发 */public class Demo11 {    public static void main(String[] args) {        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            conn = JDBCUtil.getMysqlConn();            ps = conn.prepareStatement("insert into t_user (userName) values (?)");            ps.setString(1, "小高高");            ps.execute();        } catch (Exception e) {            e.printStackTrace();        } finally{            JDBCUtil.close(rs, ps, conn);        }    }}

阅读全文

0 0