mysql_使用笔记_01

使用说明（crud）

原理步骤

1. 注册驱动
   方式1. DriverManager.registerDriver(new com.mysql.jdbc.Driver());
   方式2. Class.forName("com.mysql.jdbc.Driver");

2. 获得连接对象
   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/geminno", "root", "123");

3. 获得处理对象(statement 执行crud操作的对象)
   Statement stmt = conn.createStatement();

4. 使用Statement 进行crud操作
   
   • 查询操作 ResultSet rs = stmt.executeQuery(sql);
   • 增删改操作
5. 处理结果
   -查询操作 while(rs.next()){
System.out.print(rs.getObject(1)+"\t");}
   -增删改操作

6. 资源释放

   • 查询操作 `rs.close();

     stmt.close();

     conn.close();`

   • 增删改操作

代码示例

1. 查询

public static void query() throws Exception{
        //1.注册驱动
        //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/geminno", "root", "123");
        //3.获得处理对象(statement  执行crud操作的对象)
        Statement stmt = conn.createStatement();
        //4.使用stmt进行crud操作
        String sql = "select * from emp";
        ResultSet rs = stmt.executeQuery(sql);
        //5.处理结果
        while(rs.next()){
            System.out.print(rs.getObject(1)+"\t");
            System.out.print(rs.getObject(2)+"\t");
            System.out.print(rs.getObject(3)+"\t");
            System.out.print(rs.getObject(4)+"\t");
            System.out.print(rs.getObject(5)+"\t");
            System.out.print(rs.getObject(6)+"\t");
            System.out.println(rs.getObject(7)+"\t");

        }

        //6.资源释放
        rs.close();

        stmt.close();

        conn.close();


    }

1. 增删改

public static void executeUpdate() throws Exception{
        //1.注册驱动
        //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/geminno", "root", "123");
        //3.获得处理对象(statement  执行crud操作的对象)
        Statement stmt = conn.createStatement();
        //4.使用stmt进行crud操作
        //String sql = "UPDATE emp set salary = salary+1000 where empName='小强'";
        //String sql = "delete from test";
        String sql = "INSERT INTO `emp` VALUES ('e002', 'lili2', '', '2016-04-12 13:43:16', '20000.00', null, '3')";
        int lines = stmt.executeUpdate(sql);

        System.out.println("影响的行数:"+lines);


        //5.资源释放


        stmt.close();

        conn.close();
    }

1. 使用占位符进行预处理

public static Student checkLoginByPrepared(String name,String psd){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Student stu = null;
        try {
            conn = JdbcUtils.getConnection();
            //预处理对象   采用占位符的方式传递数据  ?
            pstmt = conn.prepareStatement("select * from tbl_student where stuName=? and tel=?");
            pstmt.setString(1, name);
            pstmt.setString(2, psd);

            rs = pstmt.executeQuery();

            if(rs.next()){
                stu = new Student(
                        rs.getInt(1),                 //获得编号
                        rs.getString("stuName"),
                        rs.getString("tel")

                );

            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JdbcUtils.free(rs, pstmt, conn);
        }

        return stu;
    }

1. jdbc 提取函数

public class JdbcUtils {

    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://localhost:3306/geminno";
    private static final String user = "root";
    private static final String psd = "123";

    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, psd);
        } catch (SQLException e) {

            //做一些日志处理
            throw e;
        }

        return conn;

    }

    public static void free(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) {

                rs.close();
                rs = null;

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {

                    stmt.close();
                    stmt = null;

                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                try {
                    if (conn != null) {

                        conn.close();
                        conn = null;

                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    public static void free(ResultSet rs, PreparedStatement pstmt, Connection conn) {
        try {
            if (rs != null) {

                rs.close();
                rs = null;

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {

                    pstmt.close();
                    pstmt = null;

                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                try {
                    if (conn != null) {

                        conn.close();
                        conn = null;

                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}