JDBC

1.什么是JDBC?

JDBC就是java数据库连接,说白了就是用java语言来操作数据库,JDBC使用java语言向数据库中发送SQL语句。

导入Jar包,加载驱动类,给出url、user、password。

使用DriverManager类得到connection对象。

jar包地址:mysql-connector-java-5.1.47.jar

package com.forcoldplay.test2;

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

import org.junit.Test;



public class Demo013 {
    
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        /*
         * jdbc四大配置参数
         * driverClassName:com.mysql.jdbc.Driver
         * url: jdbc:mysql://localhost:3306/test
         * username:root
         * password:123
         */
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "123";
        
        Connection con = DriverManager.getConnection(url,username,password);
        System.out.println(con);
    }
}

2.JDBC的增删改查

1.通过Connection对象创建Statement

Statement语句的发送器,功能是向数据库发送sql语句

2.调用它的int executeUpdate(String sql) ,它可以发送DML,DDL

增删改

package com.forcoldplay.test2;

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

import org.junit.Test;



public class Demo013 {
    
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        /*
         * jdbc四大配置参数
         * driverClassName:com.mysql.jdbc.Driver
         * url: jdbc:mysql://localhost:3306/test
         * username:root
         * password:123
         */
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "123";
        
        Connection con = DriverManager.getConnection(url,username,password);
        System.out.println(con);
        
        //通过Connection 得到Statement对象
        Statement stmt = con.createStatement();
        
        //操作test数据库里的stu6,不能在语句中加分号
        //String sql = "INSERT INTO stu6 VALUES(3,'wangwu')";
        //String sql = "UPDATE stu6 SET sname = 'suolong' WHERE sid = 3";
        String sql = "DELETE FROM stu6";
        int r  = stmt.executeUpdate(sql);
        //返回的r为影响结果的数量
        System.out.println(r);
        
    }
}

执行查询

    package com.forcoldplay.test2;

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

import org.junit.Test;



public class Demo013 {
    
    
    

    public void fun1() throws ClassNotFoundException, SQLException {
        /*
         * jdbc四大配置参数
         * driverClassName:com.mysql.jdbc.Driver
         * url: jdbc:mysql://localhost:3306/test
         * username:root
         * password:123
         */
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "123";
        
        Connection con = DriverManager.getConnection(url,username,password);
        System.out.println(con);
        
        //通过Connection 得到Statement对象
        Statement stmt = con.createStatement();
        
        //操作test数据库里的stu6,不能在语句中加分号
        String sql = "INSERT INTO stu6 VALUES(4,'zhangsan')";
        //String sql = "UPDATE stu6 SET sname = 'suolong' WHERE sid = 3";
        //String sql = "DELETE FROM stu6";
        int r  = stmt.executeUpdate(sql);
        //返回的r为影响结果的数量
        System.out.println(r);
            
    }
    
    @Test
    public void fun2() throws ClassNotFoundException, SQLException {
        /*
         * jdbc四大配置参数
         * driverClassName:com.mysql.jdbc.Driver
         * url: jdbc:mysql://localhost:3306/test
         * username:root
         * password:123
         */
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "123";
        
        Connection con = DriverManager.getConnection(url,username,password);
        System.out.println(con);
        
        //通过Connection 得到Statement对象
        Statement stmt = con.createStatement();
        
        //调用Statement的ResultSet rs = executeQuery(String querySql);
        
        ResultSet rs = stmt.executeQuery("SELECT * FROM stu6");
        
        //解析ResultSet
        //其内部有个行光标
        //提供了一个系列的getXxx()方法
        //把行光标移动到first 
        while(rs.next()) {        //把光标移动到下一行,并判断下一行是否存在
            int sid = rs.getInt("sid");
            String sname = rs.getString(2);
            System.out.println(sid+","+sname);
        }
        /*
         * 关闭资源,倒关
         * 
         */
        rs.close();
        stmt.close();
        con.close();//必须要关
    }
    
}

3.JDBC之代码规范

关闭资源有可能因为异常提前终止而执行不到,所以应该在fanlly块中

    //规范化
    @Test
    public void fun3() throws SQLException {
        
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            String driverClassName = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/test";
            String username = "root";
            String password = "123";             
            
            Class.forName(driverClassName);
            con = DriverManager.getConnection(url,username,password);
            stmt = con.createStatement();
            String sql ="SELECT * FROM stu6";
            rs = stmt.executeQuery(sql);
            
            //遍历rs ,打出其中数据
            while(rs.next()) {
                int sid = rs.getInt(1);
                String sname = rs.getNString("sname");
                System.out.println(sid + "," + sname);
            }
            
        } catch(Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭
            if(rs!=null) rs.close();
            if(stmt!=null) stmt.close();
            if(con!=null) con.close();//必须要关
        }
        
    }

4. 结果集光标

ResultSet

void beforeFirst():把光标放在第一行的前面
void afterLast():把光标放在最后一行的后面
boolean first():把光标放在第一行的位置上
boolean last():把当前光标放到最后一行的位置上
boolean previous():把光标向上挪一行
boolean next(): 把光标向下挪一行

5.结果集的特性

是否可滚动

是否敏感

是否可更新

Last modification:February 13th, 2020 at 04:33 pm
如果觉得我的文章对你有用,请随意赞赏