mysql 单表增删改查

作者: 羊1111 | 来源:发表于2018-11-01 14:31 被阅读0次

1.导入驱动jar包

创建lib目录，用于存放当前项目需要的所有jar包
选择jar包，邮件执行build path/Add to Build Path

image.png

package cn.itheima.test;

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

import org.junit.Test;

/**
 * 测试查询所有用户的类
 * @author Administrator
 *
 */
public class QueryAll {
    
    @Test
    public void testQueryAll(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            String url = "jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=GMT%2B8&useSSL=false";
            String username = "root";
            String password = "root1234";
            conn = DriverManager.getConnection(url, username, password);
            //3.获取执行sql语句对象
            stmt = conn.createStatement();
            //4.编写sql语句
            String sql = "select * from emp";
            //5.执行sql语句
            rs = stmt.executeQuery(sql);
            //6.处理结果集
            while(rs.next()){
                System.out.println("编号：" + rs.getInt(1) + "，用户名：" + rs.getString(2) + "，职位：" + rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }               
            }
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        
    }
}

sql注入

//4.创建预处理对象
PreparedStatement pstmt = conn.prepareStatement(sql);

package cn.itheima.test;

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

import org.junit.Test;

/**
 * 测试sql注入问题
 * 
 * @author Administrator
 *
 */


public class TestLogin {
    
    @Test
    public void testLogin(){
        try {
            login("zhangsan' or 'zhangsan","1234");//此时也能进入
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    
    /**
     * 用户登录方法
     * 
     * @param username
     * @param password
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void login(String username, String password) throws ClassNotFoundException, SQLException{
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=GMT%2B8&useSSL=false", "root", "root1234");
        //3.创建执行sql语句对象
        Statement stmt = conn.createStatement();
        //4.书写一个sql语句
        String sql = "select * from heima_test where " + "username='" + username+ "' and password='"+password+"'";
        //5.执行sql语句
        ResultSet rs = stmt.executeQuery(sql);
        //6.对结果集进行处理
        if(rs.next()){
            System.out.println("恭喜你，" + username +" 登录成功！");
            System.out.println(sql);
        } else {
            System.out.println("账号或密码错误！");
        }
        if(rs!=null){
            rs.close();
        }
        if(stmt!=null){
            stmt.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
    
    //防止sql注入
    public void login1(String username, String password) throws ClassNotFoundException, SQLException{
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=GMT%2B8&useSSL=false", "root", "root1234");
        //3.编写sql语句
        String sql = "select * from heima_test where username=? and password=?";
        //4.创建预处理对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //5.设置参数（给占位符）
        pstmt.setString(1, username);//从1开始
        pstmt.setString(2, password);
        //6.执行sql语句
        ResultSet rs = pstmt.executeQuery();//该方法没有参数
        //7.对结果集进行处理
        if(rs.next()){
            System.out.println("恭喜你，" + username +" 登录成功！");
            System.out.println(sql);
        } else {
            System.out.println("账号或密码错误！");
        }
        if(rs!=null){
            rs.close();
        }
        if(pstmt!=null){
            pstmt.close();
        }
        if(conn!=null){
            conn.close();
        }
    }
}

2、使用JDBC完成分类表CRUD的操作

第一种

package cn.itheima.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 提供获取连接和释放资源的方法
 * 
 * @author Administrator
 *
 */
public class JDBCUtils_V1 {
    /**
     * 获取连接方法
     * 
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=GMT%2B8&useSSL=false", "root", "root1234");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 释放资源的方法
     */
    public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

第二种：使用properties配置文件

image.png

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/bjpowernode?serverTimezone=GMT%2B8&useSSL=false
username=root
password=root1234

加载配置文件：ResourceBundle对象用静态代码块


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

/**
 * 提供获取连接和释放资源的方法
 * 
 * @author Administrator
 *
 */
public class JDBCUtils_V2 {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    
    /**
     * 静态代码块加载配置文件信息
     */
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("db");//getBundle()方法只需放入文件名
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        username = bundle.getString("username");
        password = bundle.getString("password");
        
    }
    
    
    /**
     * 获取连接方法
     * 
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            //1.注册驱动
            Class.forName(driver);
            //2.获取连接
            conn = DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

第三种：加载配置文件：Properties对象（可选）


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 提供获取连接和释放资源的方法
 * 
 * @author Administrator
 *
 */
public class JDBCUtils_V3 {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    
    /**
     * 静态代码块加载配置文件信息
     */
    static {
        try{            
            //1.通过当前类获取类加载器
            ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader();
            //2.通过类加载器的方法获得一个输入流
            InputStream is = classLoader.getResourceAsStream("db.properties");
            //3.创建一个properties对象
            Properties props = new Properties();
            //4.加载输入流
            props.load(is);
            //5.获取相关参数的值
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
            
            
        } catch(IOException e){
            e.printStackTrace();
        }
    }
    
    
    /**
     * 获取连接方法
     * 
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            //1.注册驱动
            Class.forName(driver);
            //2.获取连接
            conn = DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试增删改查（模板）

package cn.itheima.jdbc.test;

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

import org.junit.Test;

import cn.itheima.jdbc.JDBCUtils_V1;
import cn.itheima.jdbc.JDBCUtils_V2;
import cn.itheima.jdbc.JDBCUtils_V3;

/**
 * 测试工具类
 * 
 * @author Administrator
 *
 */
public class TestUntil {
    /**
     * 根据id更新用户信息方法
     */
    @Test
    public void testUpdateById(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            //1.获取连接
            conn = JDBCUtils_V3.getConnection();
            //2.编写sql语句
            String sql = "update heima_test set password=? where id=?";
            //3.获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            //4.设置参数
            pstmt.setString(1, "ffff");//第一个？位置
            pstmt.setInt(2, 222);//第二个？位置   id为222
            //5.执行更新操作
            int row = pstmt.executeUpdate();
            if(row > 0){
                System.out.println("更新成功！");
            }else{
                System.out.println("更新失败！");
            }
        } catch(Exception e){
            e.printStackTrace();
        } finally{
            JDBCUtils_V3.release(conn, pstmt, null);
        }
    }
    /**
     * 根据id删除信息方法
     */
    @Test
    public void testDeleteById(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            //1.获取连接
            conn = JDBCUtils_V3.getConnection();
            //2.编写sql语句
            String sql = "delete from heima_test where id=?";
            //3.获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            //4.设置参数
            pstmt.setInt(1, 111);//第1列为id，id为111的
            //5.执行删除操作
            int row = pstmt.executeUpdate();
            if(row > 0){
                System.out.println("删除成功！");
            }else{
                System.out.println("删除失败！");
            }
            
        } catch(Exception e){
            throw new RuntimeException(e);
        } finally{
            JDBCUtils_V3.release(conn, pstmt, null);
        }
    }
    
    
    /**
     * 添加用户信息方法
     */
    @Test
    public void testAdd(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            //1.获取连接
            conn = JDBCUtils_V2.getConnection();
            //2.编写sql语句
            String sql = "insert into heima_test values(?,?,?)";
            //3.获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            //4.设置参数
            pstmt.setString(1, "chenyang");
            pstmt.setString(2, "19960906");
            pstmt.setInt(3, 9999);
            //5.执行插入操作
            int row = pstmt.executeUpdate();
            if(row > 0){
                System.out.println("添加成功！");
            }else{
                System.out.println("添加失败！");
            }
        } catch(Exception e){
            throw new RuntimeException(e);
        } finally{
            //6.释放资源
            JDBCUtils_V2.release(conn, pstmt, null);
        }
    }
    /**
     * 根据id查询用户信息
     */
    
    @Test
    public void testfindUserById(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = JDBCUtils_V1.getConnection();
            //2.编写sql语句
            String sql = "select * from heima_test where id=?";
            //3.获取执行sql语句对象
            pstmt = conn.prepareStatement(sql);
            //4.设置参数
            pstmt.setInt(1, 9999);
            //5.执行查询操作
            rs = pstmt.executeQuery();
            //6.处理结果集
            while(rs.next()){
                System.out.println(rs.getString(1) + "-----" + rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.释放资源
            JDBCUtils_V1.release(conn, pstmt, rs);
        }
    }
}

mysql 单表增删改查

1.导入驱动jar包

sql注入

2、使用JDBC完成分类表CRUD的操作

测试增删改查（模板）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读