Java

JDBC

2018-12-15  本文已影响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);
        }
    }
}
上一篇下一篇

猜你喜欢

热点阅读