JDBC 增删改查

增删改查

package test03;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
 * JDBC 增  
 *      删
 *      改
 *      查
 * @author star
 *
 */
public class CURDTest {
    public static void main(String[] args) throws Exception {
        //insertTest();
        //deleteTest();
        //updateTest();
        selectTest();
    }
    //查
    private static void selectTest() throws Exception {
        // TODO Auto-generated method stub
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //创建连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day01", "root", "root");
        //创建Statement对象
        Statement st = conn.createStatement();
        //sql查询
        String sql = "select * from stu where math<80";
        ResultSet rs = st.executeQuery(sql);//结果集
        //显示
        while(rs.next()) {
            int id = rs.getInt(1);
            String name =rs.getString(2);
            int age = rs.getInt(3);
            int math = rs.getInt(4);
            int english = rs.getInt(5);
            System.out.println(id+" "+name+" "+age+" "+math+" "+english);
            
        }
        rs.close();
        st.close();
        conn.close();
        
        
    }

    //改
    private static void updateTest() throws Exception {
        // TODO Auto-generated method stub
        //注册驱动
        
        Class.forName("com.mysql.jdbc.Driver");
        //建立连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day01", "root", "root");
        //创建statement对象
        Statement st = conn.createStatement();
        //执行sql语句
        String sql = "update stu set name = '小马哥' where id =1";
        int i = st.executeUpdate(sql);
        //判断是否修改
        if(i!=0) {
             System.out.println("修改成功");
        }
        //关闭资源
        st.close();
        conn.close();
        
    }

    //删除
    private static void deleteTest() throws Exception {
        // TODO Auto-generated method stub
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //创建连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/day01", "root", "root");
        //创建Statement对象
        Statement st = conn.createStatement();
        //  sql注入的问题
        //     在SQL语句中使用了系统自带的关键字 or and ,让where条件判断失效
        //String sql = "delete from stu where id= 1 or 1=1";//SQL注入问题，会把整张表的内容删除
        String sql = "delete from stu where id= 1";
        int i = st.executeUpdate(sql);
        if (i!=0) {
            System.out.println("删除成功");
        }
        st.close();
        conn.close();
    }
    //增
    private static void insertTest() throws Exception {
        // TODO Auto-generated method stub
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //创建连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///day01", "root", "root");
        //创建Statement对象
        Statement st = conn.createStatement();
        //执行语句
        String sql = "insert into stu values(null,'pp','50',70,50)";
        int i = st.executeUpdate(sql);
        //判断插入是否正确
        if (i!=0) {
            System.out.println("插入成功");
        }
        st.close();
        conn.close();
        
    }
}

 

jar包导入

包： java.sql

　　javax.sql

1.加载驱动

//DriverManager.registerDriver(new Driver());

//通过源码发现Driver 类有一个静态代码块，自己创建一个对象添加进registerDriver()，我们自己又创建一个对象，造成两次注册
  //com.mysql.jdbc.Driver
  Class.forName("com.mysql.jdbc.Driver");

//获取连接对象

Connection coon = DriverManager.getConnection("jdbc:mysql://localhost:3306/day01","root","root");

//获取statement对象

Statement  st = coon.createStatement();

//执行查询

String sql = "select * from emp";

ResultSet rs = st.executeQuery(sql);

//获取结果集中的数据

while(res.next()) {
   int id = res.getInt(1);
   String name = res.getString(2);
   String age = res.getString(3);
   int math = res.getInt(4);
   int english = res.getInt(5);
   System.out.println(id+" "+name +" "+age+" "+math+" "+english);
   
  }

 

//释放资源

rs.close();

st.close();

coon.close();

package day28_test;


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

import com.mysql.jdbc.Driver;

public class JDBC_Demo {
    public static void main(String[] args) throws Exception {
        //加载驱动
        DriverManager.registerDriver(new Driver());
        //获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day01", "root", "root");
        //获取Statement
        Statement st = conn.createStatement();
        //执行查询
        String sql = "select * from stu";
        ResultSet res = st.executeQuery(sql);
        //获取结果集中的数据
        while(res.next()) {
            int id = res.getInt(1);
            String name = res.getString(2);
            String age = res.getString(3);
            int math = res.getInt(4);
            int english = res.getInt(5);
            System.out.println(id+" "+name +" "+age+" "+math+" "+english);
            
        }
        //释放资源
        res.close();
        st.close();
        conn.close();
    }
}