JAVA web基础-JDBC操作

1.基本连接操作：

    @Test    public void demo01() throws Exception{//      查询所有的分类数据//      采用的都是JDBC规范，采用的都是java。sql的包下的内容//      1.注册驱动，使用反射加载制定的类，具体加载的类以字符串体现，内容就可以存放在配置文件中，通过修改配置文件方便切换数据库//      一个类被加载到内存，静态代码块将执行。自己把自己注册驱动并进行使用。//      结论：注册驱动//      注册驱动注意事项：//      DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 注册了两次。//      第一次new driver时，driver类加载静态代码块执行，注册一次。//      第二次，手动注册        Class.forName("com.mysql.jdbc.Driver");//JAVA与mysql耦合变小，更换数据库只需要更换驱动就行。//      2.获得连接//      jdbc:mysql://ip地址：端口号/数据库名称        Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/webdb_4", "root", "1234");//      3.获得语句的执行        Statement statement =  connection.createStatement();//      4.执行SQL语句        ResultSet rs = statement.executeQuery("select * from category");//      5.处理结果        while (rs.next()) {            Integer cid = rs.getInt("cid");            String cname = rs.getString("cname");            System.out.println(cid+"\t"+cname);        }//      6.释放资源        rs.close();        statement.close();        connection.close();    }    @Test    public void test3() throws Exception{        //加载驱动        Class.forName("com.mysql.jdbc.Driver");        //获取连接Connection        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/webdb_4?user=root&password=1234");        //得到执行sql语句的对象Statement        Statement stmt = conn.createStatement();        //执行sql语句，并返回结果        ResultSet rs = stmt.executeQuery("select * from category");        //处理结果         while (rs.next()) {            Integer cid = rs.getInt("cid");            String cname = rs.getString("cname");            System.out.println(cid+"\t"+cname);        }        //关闭资源        rs.close();        stmt.close();        conn.close();    }

2.使用JDBC完成增删改操作

/* * Statement: *      st.executeUpdate(sql) 执行DML语句（增删改），返回整型，表示影响行数 *      st.executeQuery(sql) 执行DQL，返回ResultSet *  *      st.execute(sql);任意SQL语句，返回值为boolean *              true，表示执行的DQL语句，需要通过ts.getResultSet()获得查询结果 *              false，表示DML语句，需要通过ts.getUpdate（）。 * */public class JDBCDemo02 {    @Test    public void demo01() throws Exception{        Connection conn = null;        ResultSet rs = null ;        Statement st = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/webdb_4", "root","1234");            st = conn.createStatement();            rs = st.executeQuery("select * from category");            rs.previous();//结果集向前走            while (rs.next()) {                String string = rs.getString("cid");                System.out.println(string);            }        } catch (Exception e) {            // TODO: handle exception        }finally {            if (rs != null) {                try {                    rs.close();                } catch (Exception e2) {                    // TODO: handle exception                }            }            if (st != null) {                try {                    st.close();                } catch (Exception e2) {                    // TODO: handle exception                }            }            if (conn != null) {                try {                    conn.close();                } catch (Exception e2) {                    // TODO: handle exception                    e2.printStackTrace();                }            }        }    }}

3.创建JDBCUtils

public class JDBCUtils {    private static String driver = "com.mysql.jdbc.Driver";    private static String url = "jdbc:mysql://localhost:3306/webdb_4";    private static String user = "root";    private static String password = "1234";    static{//      注册驱动        try {            Class.forName(driver);        } catch (Exception e) {            // TODO Auto-generated catch block            throw new RuntimeException(e);        }    }    public static Connection getConnection() throws SQLException{//      获得连接        Connection connection = DriverManager.getConnection(url, user, password);        return connection;    }    /*     *      * */    public static void closeResource(Connection conn,Statement st,ResultSet rs){        if (rs != null) {            try {                rs.close();            } catch (Exception e2) {                // TODO: handle exception            }        }        if (st != null) {            try {                st.close();            } catch (Exception e2) {                // TODO: handle exception            }        }        if (conn != null) {            try {                conn.close();            } catch (Exception e2) {                // TODO: handle exception                e2.printStackTrace();            }        }    }}

4.SQL注入的问题

public class JDBCINput {//  SQL注入的问题，模拟用户注册    @Test    public void demo01(){        String username = "allen";        String password = "4321";        Connection connection = null;        Statement statement = null;        ResultSet resultSet = null;        try {            connection = (Connection) JDBCUtils.getConnection();            statement = connection.createStatement();            resultSet = statement.executeQuery("select * from user where username = '"+username+"' and password ='"+password+"' ");            if (resultSet.next()) {                System.out.println("login");            }else {                System.out.println("no login");            }        } catch (Exception e) {            // TODO: handle exception        }finally {            JDBCUtils.closeResource(connection, statement, resultSet);        }    }    @Test    public void demo02(){//      演示SQL注入,用户输入的SQL语句作为了SQL语言的一部分，破坏了原有的结构。        String username = "allen' #";        String password = "43212";        Connection connection = null;        Statement statement = null;        ResultSet resultSet = null;        try {            connection = (Connection) JDBCUtils.getConnection();            statement = connection.createStatement();            String sql = "select * from user where username = '"+username+"' and password ='"+password+"' ";            System.out.println(sql);            resultSet = statement.executeQuery(sql);            if (resultSet.next()) {                System.out.println("login");            }else {                System.out.println("no login");            }        } catch (Exception e) {            // TODO: handle exception        }finally {            JDBCUtils.closeResource(connection, statement, resultSet);        }    }    @Test    public void demo03(){//      解决SQL语句注入的问题。//      添加:向分类表中添加数据        Connection connection = null;        PreparedStatement preparedStatement = null;//      Statement statement = null;        ResultSet resultSet = null;        try {            connection = (Connection) JDBCUtils.getConnection();//          处理SQL语句//          获得预处理对象            String sql = "insert into user(id,username,password) values(?,?,?)";            preparedStatement = (PreparedStatement) connection.prepareStatement(sql);            preparedStatement.setInt(1, 4);            preparedStatement.setString(2, "liz");            preparedStatement.setString(3, "1234");            int r = preparedStatement.executeUpdate();            System.out.println(r);        } catch (Exception e) {            // TODO: handle exception        }finally {            JDBCUtils.closeResource(connection, preparedStatement, resultSet);        }    }    @Test    public void demo04(){        Connection connection = null;        PreparedStatement statement = null;        ResultSet resultSet = null;        try {            connection = (Connection) JDBCUtils.getConnection();            String sql = "update user set username = ? where id = ?";            statement = (PreparedStatement) connection.prepareStatement(sql);            statement.setString(1, "liz");            statement.setInt(2, 2);            int r = statement.executeUpdate();            System.out.println(r);        } catch (Exception e) {            // TODO: handle exception        }finally {            JDBCUtils.closeResource(connection, statement, resultSet);        }    }    /*     * 解决SQL注入     *      * */    @Test    public void demo05(){//      演示SQL注入,用户输入的SQL语句作为了SQL语言的一部分，破坏了原有的结构。//      String username = "allen' #";        String username = "liz";        String password = "4321";        Connection connection = null;        PreparedStatement statement = null;        ResultSet resultSet = null;        try {            connection = (Connection) JDBCUtils.getConnection();            String sql = "select * from user where username = ? and password = ?";            statement = (PreparedStatement) connection.prepareStatement(sql);            statement.setString(1, username);            statement.setString(2, password);            resultSet = statement.executeQuery();            if (resultSet.next()) {                System.out.println("login");            }else {                System.out.println("no login");            }        } catch (Exception e) {            // TODO: handle exception        }finally {            JDBCUtils.closeResource(connection, statement, resultSet);        }    }}