• JDBC数据库连接操作


    1.建立数据库、表

    如图:表结构,数据库名为test

    image

    2.建立java项目

    建立一个文件夹,找一个mysql驱动jar包丟进去,buildpath

    imageimage

    监理User实体类,重写一下toString方便查看。

    public class User {
        private int id;
        private String userName;
        private String passWord;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getUserName() {
            return userName;
        }
        public void setUserName(String userName) {
            this.userName = userName;
        }
        public String getPassWord() {
            return passWord;
        }
        public void setPassWord(String passWord) {
            this.passWord = passWord;
        }
        @Override
        public String toString() {
            return "User [id=" + id + ", userName=" + userName + ", passWord="
                    + passWord + "]";
        }
        
    
    }

    dao

    public interface IUserDao {
        public int saveUser(User user);
        public void deleteUser(User user);
        public int updateUser(User user);
        public User selectUserById(int id);
    }
    public class UserDaoImpl implements IUserDao {
    
        @Override
        public int saveUser(User user) {
            int result=0;
            try {
                Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); //获得数据库连接
                String sql="insert into user (userName,passWord)value(?,?)";
                PreparedStatement statement = conn.prepareStatement(sql); //预执行,置换参数,从1开始的
                statement.setString(1, user.getUserName());
                statement.setString(2, user.getPassWord());
                result = statement.executeUpdate();  //修改了数据的使用该方法,没有修改数据的使用execute()
            } catch (Exception e) {
                e.printStackTrace();
            }
            return result;
        }
    
        @Override
        public void deleteUser(User user) {
            int result=0;
            try {
                Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); //获得数据库连接
                String sql="delete from user where userName=?  and passWord=?";
                PreparedStatement statement = conn.prepareStatement(sql); //预执行,置换参数,从1开始的
                statement.setString(1, user.getUserName());
                statement.setString(2, user.getPassWord());
                result = statement.executeUpdate();  //修改了数据的使用该方法,没有修改数据的使用execute()
                System.out.println(result);
            } catch (Exception e) {
                e.printStackTrace();
            }
            
            
        }
    
        @Override
        public int updateUser(User user) {
            return 0;
        }
    
        @Override
        public User selectUserById(int id) {
            return null;
        }
        public static void main(String[] args) {
            UserDaoImpl userDaoImpl=new UserDaoImpl();
            User user=new User();
            user.setPassWord("123456");
            user.setUserName("帅哥");
             userDaoImpl.deleteUser(user);
        }
    }

    当然,实现着实际不能这样,起码要搞一个基本的工具类,写静态代码块的加载驱动一次、获取连接、关闭连接等操作方法。

    2.研究一下时间日期在sql和java中的关系。

    image

    数据库中对应的日期类型格式如图,date是没有时分秒的,timestamp的总体时间区间是没有datetime大的(上限)。在写入数据库的时候可以按照都转换为sql的timestamp类型写入数据库,在数据库会自动变成它们定义的类型。

    @Override
        public int saveUser(User user) {
            int result=0;
            try {
                Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); //获得数据库连接
                String sql="insert into user (userName,passWord,date,datetime,timestamp)value(?,?,?,?,?)";
                PreparedStatement statement = conn.prepareStatement(sql); //预执行,置换参数,从1开始的
                statement.setString(1, user.getUserName());
                statement.setString(2, user.getPassWord());
                statement.setTimestamp(3, new Timestamp(user.getDate().getTime())); //需要转换一下才行
                statement.setTimestamp(4, new Timestamp(user.getDatetime().getTime()));
                statement.setTimestamp(5, user.getTimestamp());
                result = statement.executeUpdate();  //修改了数据的使用该方法,没有修改数据的使用execute()
            } catch (Exception e) {
                e.printStackTrace();
            }
            return result;
        }

    取出来:要转换一下,否则会丢失了精度。还有后面还有.0,贼麻烦。建议统统使用string记录就好了

    public User selectUserById(int id) {
            User user=null;
            try {
                Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行
                Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
                String sql="select * from user where id = 5";
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery(sql);
                while(resultSet.next()){
                    user=new User();
                    user.setId(resultSet.getInt(1));
                    user.setPassWord(resultSet.getString(2));
                    user.setUserName(resultSet.getString("userName"));
                    user.setDate(resultSet.getDate("date"));
    //                user.setDatetime(resultSet.getDate(5)); //这里数据库使用的类型为datetime=YYYY-MM-DD HH:MM:SS,取出来就没了时分秒了
                    user.setDatetime(resultSet.getTimestamp(5)); //使用这个timestamp就可以获得全。
                    user.setTimestamp(resultSet.getTimestamp("timestamp"));
                }
                
            } catch (Exception e) {
                e.printStackTrace();
            }
            return user;
        }

    3.数据库写入图片。正常没什么卵用的东西,还不如直接存在服务器上,搞在数据库压力很大的。

    public class TestBlob {
        public static void main(String[] args) {
            //add();
            read();
        }
        
        public static void read(){
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            
            try {
                connection = JDBCUtil.getConnection();
                String sql = "select * from user where id=?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, 8);
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    //InputStream inputStream = resultSet.getBinaryStream("pic");
                    Blob blob = resultSet.getBlob("pic");
                    InputStream inputStream = blob.getBinaryStream();
                    OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(new File("d:/bak4.jpg")));
                    byte[] buffer = new byte[1024];
                    for(int i=0;(i=inputStream.read(buffer))>0;){
                        outputStream.write(buffer, 0, i);
                    }
                    inputStream.close();
                    outputStream.close();
                }
                
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }finally{
                JDBCUtil.free(resultSet, preparedStatement, connection);
            }
        }
        
        
    
        /**
         * 
         */
        public static void add() {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            try {
                // 在指定的文件上插入读取管道
                File file = new File("d:/4.jpg");
                InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
                connection = JDBCUtil.getConnection();
                String sql = "insert into user (username,password,pic) values (?,?,?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, "root");
                preparedStatement.setString(2, "root");
                preparedStatement.setBinaryStream(3, inputStream, file.length());
                preparedStatement.executeUpdate();
                inputStream.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                JDBCUtil.free(null, preparedStatement, connection);
            }
        }
    }

    4.写入大文本文件。这个有时候还有点用

    public class TestText {
    
        public static void main(String[] args) {
    
            // add();
            read();
        }
    
        public static void read() {
            Connection con = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultset = null;
    
            try {
    
                Writer writer = new BufferedWriter(new FileWriter(
                        "c:/bak_db_kaoqin.txt"));
    
                con = JDBCUtil.getConnection();
                String sql = "select * from user where id=?";
                preparedStatement = con.prepareStatement(sql);
                preparedStatement.setInt(1, 12);
    
                resultset = preparedStatement.executeQuery();
                if (resultset.next()) {
                    char[] date = new char[1024];
                    int length = 0;
                    Reader reader = resultset.getCharacterStream("info");
                    while ((length = reader.read(date)) != -1) {
                        writer.write(date, 0, length);
    
                    }
    
                    reader.close();
                }
                writer.close();
    
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                JDBCUtil.free(resultset, preparedStatement, con);
            }
    
        }
    
        public static void add() {
            Connection con = null;
            PreparedStatement preparestatement = null;
    
            try {
                File file = new File("c:/db_kaoqin.sql");
                Reader reader = new BufferedReader(new FileReader(file));
    
                con = JDBCUtil.getConnection();
                String sql = "insert into user (username,info) values(?,?)";
                preparestatement = con.prepareStatement(sql);
    
                preparestatement.setString(1, "root");
                preparestatement.setCharacterStream(2, reader, file.length());
    
                preparestatement.executeUpdate();
    
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } finally {
                JDBCUtil.free(null, preparestatement, con);
            }
    
        }
    
    }

    好了,如有不足或者错误请各位指出,虚心请教!

    5.批量sql操作

    @Override
        public int allExcuse() throws Exception {
            String sql1="insert into user (userName) value(?)";
            
            Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
            PreparedStatement statement = connection.prepareStatement(sql1);
            statement.setString(1, "张三");
            statement.addBatch();
            statement.setString(1, "李四");
            statement.addBatch();
            statement.setString(1, "王五");
            statement.addBatch();
            statement.setString(1, "甲");
            statement.addBatch();
            statement.setString(1, "乙");
            statement.addBatch();
            statement.setString(1, "丙");
            statement.addBatch();
            
            int[] batch = statement.executeBatch();
            System.out.println(batch);
            return 0;
        }
  • 相关阅读:
    semijoin链接进行subquery unnesting.
    CONCATENATION 引发的性能问题
    身份证号码有效性检测算法 ( js版 转 C#版 )
    【CS Round #48 (Div. 2 only)】8 Divisible
    【CS Round #48 (Div. 2 only)】Water Volume
    【CS Round #48 (Div. 2 only)】Game of Chance
    【】queue
    【】maze
    【】minimum
    【AtCoder Beginner Contest 073 D】joisino's travel
  • 原文地址:https://www.cnblogs.com/lq625424841/p/7285565.html
Copyright © 2020-2023  润新知