2.JDBC学习（MySQL）

1 JdbcUtils类：配置数据库的相关信息。

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 JdbcUtils {private static String url = "jdbc:mysql://10.105.43.220:3306/jdbc";private static String user = "sa";private static String password = "123456";private JdbcUtils() {}static {try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}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();}}}}}

2 JdbcUtilsSing类：该类是JdbcUtils类的单例实现形式，与JdbcUtils类互斥使用。

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 JdbcUtilsSing {private static String url = "jdbc:mysql://10.105.43.220:3306/jdbc";private static String user = "sa";private static String password = "123456";// private static JdbcUtilsSing instance = new JdbcUtilsSing();private static JdbcUtilsSing instance = null;private JdbcUtilsSing() {}public static JdbcUtilsSing getInstance() {if (instance == null) {synchronized (JdbcUtilsSing.class) {if (instance == null) {instance = new JdbcUtilsSing();}}}return instance;}static {try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {throw new ExceptionInInitializerError(e);}}public Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}public 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();}}}}}

3 Base类：使用封装类的一个例子。

package cn.itcast.jdbc;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;public class Base {/** * @param args * @throws Exception */public static void main(String[] args) throws Exception {template(); }/** * @throws Exception */static void template() throws Exception {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句st = conn.createStatement();// 4.执行语句rs = st.executeQuery("select * from user");// 5.处理结果while (rs.next()) {// 参数中的1,2,3,4是指sql中的列索引System.out.println(rs.getObject(1) + "\t" + rs.getObject(2)+ "\t" + rs.getObject(3) + "\t" + rs.getObject(4));}} finally {JdbcUtils.free(rs, st, conn);}}}

4 CRUB类：使用封装类完成最简单的增删查改工作。

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();//update();//delete();}static void delete() throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句st = conn.createStatement();String sql = "delete from user where id>4";// 4.执行语句int i = st.executeUpdate(sql);System.out.println("i=" + i);} finally {JdbcUtils.free(rs, st, conn);}}static void update() throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句st = conn.createStatement();String sql = "update user set money=money+10 ";// 4.执行语句int i = st.executeUpdate(sql);System.out.println("i=" + i);} finally {JdbcUtils.free(rs, st, conn);}}static void create() throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句st = conn.createStatement();String sql = "insert into user(name,birthday, money) values ('name1', '1987-01-01', 400) ";// 4.执行语句int i = st.executeUpdate(sql);System.out.println("i=" + i);} finally {JdbcUtils.free(rs, st, conn);}}static void read() throws SQLException {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句st = conn.createStatement();// 4.执行语句rs = st.executeQuery("select id, name, money, birthday  from user");// 5.处理结果while (rs.next()) {System.out.println(rs.getObject("id") + "\t"+ rs.getObject("name") + "\t"+ rs.getObject("birthday") + "\t"+ rs.getObject("money"));}} finally {JdbcUtils.free(rs, st, conn);}}}

5 SQLInject：使用PreparedStatement代替Statement进行操作。
注：尽量使用PreparedStatement，因为速度比较快；当有字符串类型时建议使用PreparedStatement。

package cn.itcast.jdbc;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class SQLInject {/** * @param args * @throws SQLException */public static void main(String[] args) throws SQLException {read("name1");}static void read(String name) throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句String sql = "select id, name, money, birthday  from user where name=?";ps = conn.prepareStatement(sql);ps.setString(1, name);// 4.执行语句rs = ps.executeQuery();// 5.处理结果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);}}}

5 DateTest类：处理数据库中时间这种方式。
  由于在Java中java.until中有Data类、java.sql中也有Date这个类，我们在程序中只需要和java.util进行打交道，因此优先使用java.util中的Date类，java.sql中的Date类是java.util中的Date类的子类，只在和数据库进行交互时才使用，必要时进行转换即可。java.util中的Date类是完整的时间（包括时分秒），而java.sql中的Date类则知道日期就结束了。

package cn.itcast.jdbc;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Date;public class DateTest {static void create(String name, Date birthday, float money)throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句String sql = "insert into user(name,birthday, money) values (?, ?, ?) ";ps = conn.prepareStatement(sql);ps.setString(1, name);ps.setDate(2, new java.sql.Date(birthday.getTime()));ps.setFloat(3, money);// 4.执行语句int i = ps.executeUpdate();System.out.println("i=" + i);} finally {JdbcUtils.free(rs, ps, conn);}}}

6 ClobTest类：访问大段文本数据实例。
在实例中有两种方式对大段文本数据进行存取，建议使用String类型，因为更易于理解。

package cn.itcast.jdbc;import java.io.BufferedReader;import java.io.BufferedWriter;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.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 {/** * @param args * @throws IOException * @throws SQLException */public static void main(String[] args) throws SQLException, IOException {//create();read();}static void read() throws SQLException, IOException {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句st = conn.createStatement();// 4.执行语句rs = st.executeQuery("select bigtext  from clobtest");// 5.处理结果while (rs.next()) {//Clob clob = rs.getClob(1);//Reader reader = clob.getCharacterStream();// reader = rs.getCharacterStream(1);String s = rs.getString(1);File file = new File("JdbUtils_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);//}writer.write(s);writer.close();//reader.close();}} finally {JdbcUtils.free(rs, st, conn);}}static void create() throws SQLException, IOException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句String sql = "insert into clobtest(bigtext) values (?) ";ps = conn.prepareStatement(sql);File file = new File("src/cn/itcast/jdbc/JdbcUtils.java");//Reader reader = new BufferedReader(new FileReader(file));BufferedReader reader = new BufferedReader(new FileReader(file));//ps.setCharacterStream(1, reader, (int) file.length());// 读取一行            String line = null;            StringBuffer strBuffer = new StringBuffer();                             while ((line = reader.readLine()) != null)            {                strBuffer.append(line);                strBuffer.append("\n");            }  ps.setString(1, strBuffer.toString());//一定要注意：可以使用String类型处理MySQL数据库中的text类型，不比和上面这么麻烦。// 4.执行语句int i = ps.executeUpdate();reader.close();System.out.println("i=" + i);} finally {JdbcUtils.free(rs, ps, conn);}}}

6 BlobTest类：访问二进制类型的数据。
注意：二进制类型的数据包括图片等，而采用这种方式的时候就不可以使用字符进行处理了，当长度不够时采用更大类型的二进制类型。

package cn.itcast.jdbc;import java.io.BufferedInputStream;import java.io.BufferedOutputStream;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.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class BlobTest {/** * @param args * @throws IOException * @throws SQLException */public static void main(String[] args) throws SQLException, IOException {//create();read();}static void read() throws SQLException, IOException {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句st = conn.createStatement();// 4.执行语句rs = st.executeQuery("select bigbit from blobtest");// 5.处理结果while (rs.next()) {// Blob blob = rs.getBlob(1);// InputStream in = blob.getBinaryStream();InputStream in = rs.getBinaryStream("bigbit");File file = new File("Desert_bak.jpg");OutputStream out = new BufferedOutputStream(new FileOutputStream(file));byte[] buff = new byte[1024];for (int i = 0; (i = in.read(buff)) > 0;) {out.write(buff, 0, i);}out.close();in.close();}} finally {JdbcUtils.free(rs, st, conn);}}static void create() throws SQLException, IOException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 2.建立连接conn = JdbcUtils.getConnection();// conn = JdbcUtilsSing.getInstance().getConnection();// 3.创建语句String sql = "insert into blobtest(bigbit) values (?) ";ps = conn.prepareStatement(sql);File file = new File("Desert.jpg");InputStream in = new BufferedInputStream(new FileInputStream(file));ps.setBinaryStream(1, in, (int) file.length());// 4.执行语句int i = ps.executeUpdate();in.close();System.out.println("i=" + i);} finally {JdbcUtils.free(rs, ps, conn);}}}