JDBC

作者: 强某某 | 来源:发表于2018-12-15 15:31 被阅读8次

    基本使用流程


    1. 在src同级目录创建lib文件夹,把mysql驱动包添加进去并build path
      2.以下式代码基本使用流程
    package com.tjsmc.view;
    
    import java.security.interfaces.RSAKey;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import com.mysql.jdbc.Driver;
    
    public class One {
        
        public static void main(String[] args) {
            Connection connection=null;
            Statement createStatement=null;
            ResultSet executeQuery=null;
            //ctrl+t  鼠标移动到对应的单词,ctrl+t显示继承关系
            try {
                //1.注册驱动
                DriverManager.registerDriver(new Driver());
                //2.建立连接 ctrl+1自动完成变量
                 connection = DriverManager.getConnection("jdbc:mysql://localhost/java", "root", "zengqiang");
                //3.创建Statement 和数据库交互必须有Statement
                 createStatement = connection.createStatement();
                
                String sql="select * from student";
                //4.执行查询
                 executeQuery = createStatement.executeQuery(sql);
                while (executeQuery.next()) {
                    String name = executeQuery.getString("name");
                    String sex = executeQuery.getString("sex");
                    int age=executeQuery.getInt("age");
                    System.out.println(name+"..."+age+"..."+sex);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                //关闭和创建逆向
                if (executeQuery!=null) {
                    try {
                        executeQuery.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (createStatement!=null) {
                    try {
                        createStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection!=null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                
            }
        }
    
    }
    

    JDBC简单工具类

    package com.tjsmc.view;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.InputStream;
    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 String driverClass=null;
        static String url=null;
        static String name=null;;
        static String password=null;
        
        //静态代码块,随着类的加载而加载
        static{ 
            Properties properties=new Properties();
            //使用类加载器获取src目录下的资源文件
    //      InputStream iStream=JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            //下面等效,获取项目根目录下的资源文件(所以虽然两种都可以加载资源文件,但是资源文件对应的地址不同)
            InputStream iStream=null;
            try {
                iStream = new FileInputStream("jdbc.properties");
            } catch (FileNotFoundException e1) {
                e1.printStackTrace();
            }
            try {
                properties.load(iStream);
                driverClass=properties.getProperty("driverClass");
                url=properties.getProperty("url");
                name=properties.getProperty("name");
                password=properties.getProperty("password");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        /**
         * 获取连接
         * @return
         */
        public static Connection getConn() {
            Connection conn=null;
            try {
                //从jdbc4.0开始注册驱动可以不注册,下面一句代码可省略
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(url,name,password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }
        
        /**
         * 释放资源
         * @param conn
         * @param st
         * @param rs
         */
        public static void release(Connection conn,Statement st,ResultSet rs) {
            closeConn(conn);
            closeRs(rs);
            closeSt(st);
        }
        
        /**
         * 释放资源的重载
         * @param conn
         * @param st
         */
        public static void release(Connection conn,Statement st) {
            closeConn(conn);
            closeSt(st);
        }
    
        private static void closeRs(ResultSet rs) {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                rs = null;
            }
        }
    
        private static void closeSt(Statement st) {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                st = null;
            }
        }
    
        private static void closeConn(Connection conn) {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                conn = null;
            }
        }
    }
    
    
    jdbc.properties文件:
    driverClass=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost/java
    name=root
    password=zengqiang
    

    单元测试


    1. 右键项目,build path
    2. add libraries条目
    3. 选择junit4
    4. 新建test类或者规划包
    5. 在要进行单元测试的方法上面加上@Test
    6. 选中方法名右键 run junit test即可
    package com.tjsmc.view.test;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import org.junit.Test;
    
    import com.tjsmc.view.JDBCUtil;
    
    public class TestDemo {
        @Test
        public void testQuery() {
            Connection conn=null;
            Statement st=null;
            ResultSet rs=null;
            try {
                 conn = JDBCUtil.getConn();
                 st = conn.createStatement();
                String sql="select * from student";
                 rs = st.executeQuery(sql);
                while (rs.next()) {
                    String name = rs.getString("name");
                    String sex = rs.getString("sex");
                    int age=rs.getInt("age");
                    System.out.println(name+"..."+age+"..."+sex);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, st, rs);
            }
        }
        
        @Test
        public void testInsert() {
            Connection conn=null;
            Statement st=null;
            try {
                 conn = JDBCUtil.getConn();
                 st = conn.createStatement();
                String sql="insert into student values('彭',20,'1')";
                int executeUpdate = st.executeUpdate(sql);
                if (executeUpdate>0) {
                    System.out.println("添加成功");
                }else{
                    System.out.println("添加失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, st);
            }
        }
        
        @Test
        public void testDelete() {
            Connection conn=null;
            Statement st=null;
            try {
                 conn = JDBCUtil.getConn();
                 st = conn.createStatement();
                String sql="delete from student where name='彭'";
                int executeUpdate = st.executeUpdate(sql);
                if (executeUpdate>0) {
                    System.out.println("删除成功");
                }else{
                    System.out.println("删除失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, st);
            }
        }
        
        @Test
        public void testUpdate() {
            Connection conn=null;
            Statement st=null;
            try {
                 conn = JDBCUtil.getConn();
                 st = conn.createStatement();
                String sql="update  student set age=100 where name='强'";
                int executeUpdate = st.executeUpdate(sql);
                if (executeUpdate>0) {
                    System.out.println("更新成功");
                }else{
                    System.out.println("更新失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, st);
            }
        }
    }
    
    

    Statement隐患


    statement执行时先拼接字符串然后执行sql语句,有安全隐患。

    基本案例如下:
    @Test
        public void testQuery() {
            Connection conn=null;
            Statement st=null;
            ResultSet rs=null;
            try {
                 conn = JDBCUtil.getConn();
                 st = conn.createStatement();
                String sql="select * from student where name='曾' and age=5";
                 rs = st.executeQuery(sql);
                if (rs.next()) {
                    System.out.println("登录成功");
                }else{
                    System.out.println("登录失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, st, rs);
            }
        }
    结果是:登录失败
    
    安全隐患案例:
    @Test
        public void testQuery() {
            Connection conn=null;
            Statement st=null;
            ResultSet rs=null;
            try {
                 conn = JDBCUtil.getConn();
                 st = conn.createStatement();
                 //此处是唯一区别
                String sql="select * from student where name='曾' and age=5 or 1=1";
                 rs = st.executeQuery(sql);
                if (rs.next()) {
                    System.out.println("登录成功");
                }else{
                    System.out.println("登录失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, st, rs);
            }
        }
        结果是:登录成功,因为全面条件不论怎么样,但是1=1是永远成立的
    
    登录注册案例:
    @Test
        public void testQuery() {
            String name="assa";
            String age="20 or 1=1";
            Connection conn=null;
            Statement st=null;
            ResultSet rs=null;
            try {
                 conn = JDBCUtil.getConn();
                 st = conn.createStatement();
                String sql="select * from student where name='"+name+"' and age="+age;
                 rs = st.executeQuery(sql);
                 //查询登录一般结果只有一个或者没有
                if (rs.next()) {
                    System.out.println("登录成功");
                }else{
                    System.out.println("登录失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, st, rs);
            }
        }
    

    PrepareStatement

    预编译的Statement用于解决Statement的安全问题,通过占位符进行预编译,索引从1开始

    单元测试实例类:
    package com.tjsmc.view.test;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import org.junit.Test;
    
    import com.tjsmc.view.JDBCUtil;
    
    public class TestDemo {
        @Test
        public void testQuery() {
            String name="曾";
            String sex="30 or 1=1";
    //      String sex="30";
            Connection conn=null;
            PreparedStatement ps=null;
            ResultSet rs=null;
            try {
                 conn = JDBCUtil.getConn();
                 String sql="select * from student where name=? and sex=?";
                  ps = conn.prepareStatement(sql);
                  ps.setString(1, name);
                  ps.setString(2, sex);
                  ////注意此处executeQuery内部已经没有传参了
                 rs = ps.executeQuery();
                if (rs.next()) {
                    System.out.println("登录成功");
                }else{
                    System.out.println("登录失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, ps, rs);
            }
        }
        
        @Test
        public void testInsert() {
            Connection conn=null;
            PreparedStatement ps=null;
            try {
                 conn = JDBCUtil.getConn();
                 String sql="insert into student values(?,?,?)";
                 ps = conn.prepareStatement(sql);
                 ps.setString(1, "彭");
                 ps.setInt(2,20);
                 ps.setString(3, "3");
                 //注意此处executeUpdate内部已经没有传参了
                int executeUpdate = ps.executeUpdate();
                if (executeUpdate>0) {
                    System.out.println("添加成功");
                }else{
                    System.out.println("添加失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, ps);
            }
        }
        
        @Test
        public void testDelete() {
            Connection conn=null;
            PreparedStatement ps=null;
            try {
                 conn = JDBCUtil.getConn();
                 String sql="delete from student where name=?";
                 ps = conn.prepareStatement(sql);
                 ps.setString(1, "彭");
                int executeUpdate = ps.executeUpdate();
                if (executeUpdate>0) {
                    System.out.println("删除成功");
                }else{
                    System.out.println("删除失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, ps);
            }
        }
        
        @Test
        public void testUpdate() {
            Connection conn=null;
            PreparedStatement ps=null;
            try {
                 conn = JDBCUtil.getConn();
                 String sql="update  student set age=200 where name=?";
                 ps = conn.prepareStatement(sql);
                 ps.setString(1, "强");
                int executeUpdate = ps.executeUpdate();
                if (executeUpdate>0) {
                    System.out.println("更新成功");
                }else{
                    System.out.println("更新失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtil.release(conn, ps);
            }
        }
    }
    
    

    相关文章

      网友评论

        本文标题:JDBC

        本文链接:https://www.haomeiwen.com/subject/uwjahqtx.html