JDBC的使用

工具类

 1 package cn.itcast.jdbc;
 2 
 3 import java.sql.*;
 4 
 5 /**
 6  * Created by sherry on 000019/5/19 22:54.
 7  */
 8 public final class JdbcUtils {
 9     private static String url = "jdbc:mysql://localhost:3306/ssi";
10     private static String username = "root";
11     private static String password = "123456";
12 
13     private JdbcUtils() {
14     }
15 
16     static {
17         try {
18             Class.forName("com.mysql.jdbc.Driver");
19         } catch (ClassNotFoundException e) {
20             e.printStackTrace();
21             throw new ExceptionInInitializerError(e);
22         }
23     }
24 
25     public static Connection getCollection() throws SQLException {
26         return DriverManager.getConnection(url, username, password);
27     }
28 
29     public static void close(Connection connection,Statement statement,ResultSet resultSet){
30         if (resultSet!=null){
31             try {
32                 resultSet.close();
33             } catch (SQLException e) {
34                 e.printStackTrace();
35             }finally {
36                 if (statement!=null){
37                     try {
38                         statement.close();
39                     } catch (SQLException e) {
40                         e.printStackTrace();
41                     }finally {
42                         if (connection!=null){
43                             try {
44                                 connection.close();
45                             } catch (SQLException e) {
46                                 e.printStackTrace();
47                             }
48                         }
49                     }
50                 }
51             }
52         }
53     }
54 }

CRUD

package cn.itcast.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by sherry on 000019/5/19 23:06.
 */
public class CRUD {
    public static void main(String[] args) {
    }

    static void create(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();
            statement = connection.createStatement();
            String sql = "INSERT into user(username,password,birthday) VALUES" +
                    "('name1','password1','1990-01-01')";
            int i = statement.executeUpdate(sql);
            System.out.println(i+"条记录被插入");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,statement,resultSet);
        }

    }
    static void read(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT id,username,password,birthday FROM user");
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("username");
                String pass = resultSet.getString("password");
                String birth = resultSet.getString("birthday");
                System.out.println(id+"\t"+
                        name+"\t"+
                        pass+"\t"+
                        birth);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,statement,resultSet);
        }

    }

    static void update(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();
            statement = connection.createStatement();
            String sql = "UPDATE user set username = 'name2' WHERE id = 21";
            int i = statement.executeUpdate(sql);
            System.out.println(i+"条记录被修改");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,statement,resultSet);
        }

    }

    static void delete(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();
            statement = connection.createStatement();
            String sql = "DELETE FROM user WHERE id < 10";
            int i = statement.executeUpdate(sql);
            System.out.println(i+"条记录被删除");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,statement,resultSet);
        }

    }
}

SQL注入问题

package cn.itcast.jdbc;

import java.sql.*;

/**
 * Created by sherry on 000020/5/20 18:02.
 */
public class SQLInject {
    public static void main(String[] args) {
        read("name2");
    }

    static void read(String name){
        Connection connection = null;
        //解决SQL注入问题
        PreparedStatement preparedStatement = null;
        ResultSet resulSet = null;

        try {
            connection = JdbcUtils.getCollection();

            String sql = "SELECT id,usernme,password,birthday FROM user WHERE username = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getInt("id")+"\t"+
                        resultSet.getString("username")+"\t"+
                        resultSet.getString("password")+"\t"+
                        resultSet.getString("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,preparedStatement,resultSet);
        }

    }
}

复杂数据类型的处理

package cn.itcast.jdbc;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by sherry on 000020/5/20 19:13.
 */
public class BlobTest {
    public static void main(String[] args) {

    }

    static void create(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();

            String sql = "INSERT into blob_test(big_bin) VALUES (?)";
            preparedStatement = connection.prepareStatement(sql);
            File file = new File("需要读取的二进制文件的路径");
            InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
            preparedStatement.setBlob(1,inputStream);
            int i = preparedStatement.executeUpdate();

            System.out.println(i+"条记录被插入");
            inputStream.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,preparedStatement,resultSet);
        }

    }

    static void read(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();
            String sql = "SELECT big_bin FROM blob_test";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()){
                File file =  new File("文件写入路径");
                InputStream inputStream = resultSet.getBinaryStream("big_bin");
                OutputStream outputStream =  new BufferedOutputStream(new FileOutputStream(file));
                byte[] bytes = new byte[1024];
                for (int i; (i = inputStream.read(bytes))>0;) {
                    outputStream.write(bytes,0,i);
                }
                outputStream.close();
                inputStream.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,statement,resultSet);
        }

    }
}

package cn.itcast.jdbc;

import java.io.*;
import java.sql.*;

/**
 * Created by sherry on 000020/5/20 18:53.
 */
public class ClobTest {
    public static void main(String[] args) {

    }

    static void create(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();

            String sql = "INSERT into clob_test(big_text) VALUES (?)";
            preparedStatement = connection.prepareStatement(sql);
            File file = new File("需要读取的文件的路径");
            Reader reader = new BufferedReader(new FileReader(file));
            preparedStatement.setCharacterStream(1,reader,file.length());
            int i = preparedStatement.executeUpdate();

            System.out.println(i+"条记录被插入");
            reader.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,preparedStatement,resultSet);
        }

    }

    static void read(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();
            String sql = "SELECT big_text FROM clob_test";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()){
                File file =  new File("文件写入路径");
                Reader reader = resultSet.getCharacterStream("big_text");
                Writer writer = new BufferedWriter(new FileWriter(file));
                char[] buff = new char[1024];
                for (int i;(i = reader.read(buff))>0;){
                    writer.write(buff,0,i);
                }
                writer.close();
                reader.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,statement,resultSet);
        }

    }
}

package cn.itcast.jdbc;

import java.sql.*;
import java.util.Date;

/**
 * Created by sherry on 000020/5/20 18:39.
 */
public class DateTest {
    public static void main(String[] args) {
        create("zln","123","2022-22-22",new Date());
    }


    static void create(String username,String password,String birthday,Date startDate){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getCollection();
            String sql = " INSERT into user(username,password,birthday,startday) VALUES (?,?,?,?) ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            preparedStatement.setString(3,birthday);
            //util包中的Date转型到sql包中的Date
            preparedStatement.setDate(4, new java.sql.Date(startDate.getTime()));
            int i = preparedStatement.executeUpdate();
            System.out.println(i+"条记录被插入");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection,preparedStatement,resultSet);
        }

    }
}

posted @ 2015-06-13 18:43 csnmd 阅读(165) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

KoKo

JDBC的使用

公告