五月二十五日jdbc基础知识点

Jdbc连接数据库
1.建立与数据库的连接
1.1导入jdbc包
1.2加载JDBC驱动
java.lang.Class.forName(JDBCDriverClass);
Class.forName(driver);
MySQL的加载驱动
Class.forName(com.mysql.jdbc.Driver);
1.3获取数据库连接对象
Connection conn = DriverManager.getConnection(url,user,password);
2执行SQL语句
步骤一：创建Statement对象
步骤二：调用Statement对象相应方法将Sql语句发送到所连接数据库
2.1创建执行对象
Statement stmt = conn.createStatement();
2.2执行查询语句：
public ResultSet executeQuery(String selectSql)
ResultSet rs = stmt.executeQuery(selectSql);
2.3 执行增删改语句：
public ResultSet executeUpdate(String updateSql);
Result rs = stmt.executeUpdate(updateSql);
3.处理返回结果
while(rs.next()){
String no = rs.getString("sNo");
String name = rs.getString("sName");
String sex = rs.getString("sex");
Int age = rs.getInt("age");
String dept = rs.getString("dept");
String address = rs.getString("address");
System.out.println(no+"\t"+name+"\t"+sex+"\t"+age+"\t"+dept+"\t"+address));
};
4.关闭创建的各种对象
步骤1：关闭结果集对象rs
步骤2：关闭执行对象stmt
步骤3：关闭数据库连接对象conn
try{
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(conn!=null) conn.close();}
catch(Exeption e){e.printStackTrace();}

public class app17_15 {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/ku";
    private static String user = "root";
    private static String password = "root";
    public static void main(String[] args) {
        String sql = "SELECT sNo,sName,sex,age,dept,address FROM student WHERE dept = '计算机'";
        try(
                //创建数据库连接
                Connection conn= DriverManager.getConnection(url,user,password);
                //执行SQL语句
                Statement stmt = conn.createStatement();
                //处理返回结果
                ResultSet rs = stmt.executeQuery(sql);
                ){
            Class.forName(driver);//加载MySQL的驱动
            while(rs.next()){
                String no = rs.getString("sNo");
                String name = rs.getString("sName");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                String dept = rs.getString("dept");
                String address = rs.getString("address");
                System.out.println(no+"\t"+name+"\t"+sex+"\t"+age+"\t"+dept+"\t"+address);
            }
        }
                catch (Exception e){
            e.printStackTrace();
                }
    }
}

 

5.jdbc中三种SQL对表查询方式
第一种：Statement接口执行不带参数的静态SQL语句查询
静态SQL语句指执行executeQuery()以及executeUpdate()方法时，
SQL语句没有参数
第二种：PreparedStatement接口执行动态SQL语句查询
第三种：CallableStatement接口执行存储过程调用

 

public class app17_16 {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/ku";
    private static String user = "root";
    private static String password = "root";
    public static void main(String[] args) {
        String selectSql = "SELECT * FROM student WHERE dept = '计算机'";
        String insertSql = "INSERT INTO student(sNo,sName,sex,age,dept,address) VALUES('9','刘六','女',22,'外语','湖南')";
        String updateSql = "UPDATE student SET dept ='金融' WHERE sNo = '7'";
        String deleteSql = "DELETE FROM student WHERE sNo = '3'";
        try(
                Connection conn  = DriverManager.getConnection(url,user,password);
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(selectSql);
                ){
            Class.forName(driver);
            while(rs.next()) {
                String no = rs.getString("sNo");
                String name = rs.getString("sName");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                String dept = rs.getString("dept");
                String address = rs.getString("address");
                System.out.println(no + "\t" + name + "\t" + sex + "\t" + age + "\t" + dept + "\t" + address);
            }
            {
                int count = stmt.executeUpdate(insertSql);
                System.out.println("添加了"+count+"记录到student表");
                count = stmt.executeUpdate(updateSql);
                System.out.println("修改了"+count+"记录到student表");
                count = stmt.executeUpdate(deleteSql);
                System.out.println("删除了"+count+"记录到student表");
            }
        }
                catch (Exception e){
            e.printStackTrace();
                }
    }
}

 

总结：熟悉了一遍数据库与idea的连接

创建数据库连接对象

创建SQL执行对象

处理返回结果集

关闭对象

令我耗时的是程序出来咯，但由于没有导入jar包，导致出错