创建表DDL 、增删改DML、DQL查询操作

1.创建一张Student表，创建表DDL 

import static org.junit.Assert.*;

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

import org.junit.Test;

/**
 * 创建一个学生表，
 * SQL语句：create table t_student(id int prinmary key auto_increment,name varchar(20),age int)
 * 
*/

public class DDLTest {
    private static final String str = null;
    
    @Test
    public void testCreateTable() throws Exception {
        //贾琏欲执事，贾--加(贾)载注册驱动，琏--获取数据库连(琏)接对象 ，创建语(欲)句，执(执)行SQL语句，释(事)放资源(先开后关)
        String sql = "create table t_student (id int primary key auto_increment,name varchar(50),age int)";
        
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2.获取数据库连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root");
        
        //3.创建语句
        Statement st = conn.createStatement();
        
        //4.执行SQL语句
        st.executeUpdate(sql);
        
        
        //5.释放资源(先开后关)
        st.close();
        conn.close();
    }
    
    
}

增删改DML：

import static org.junit.Assert.*;

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

import org.junit.Test;
//插入数据 SQL:insert into t_student (name,age) values('小乔',30)
public class DMLTest {
    
    
    @Test
    public void testInsert() throws Exception {
        String sql = "insert into t_student (name, age) values('大桥',30)";
        //贾琏欲执事
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2.获取数据库连接对象
        Connection conne = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root");
        
        //3.创建语句
        Statement St = conne.createStatement();
        
        //4.执行SQL语句
        St.executeUpdate(sql);
        
        //5.释放空间(先开后关)
        St.close();
        conne.close();
    }    
        /*
         * 
         * 修改操作：update t_student set name = '貂蝉',age = 35 where id = 3
         * 
         * 
         */
        @Test
        public void testUpdate() throws Exception {
            String sql = "update t_student set name = '貂蝉',age = 35 where id =3";
            //贾琏欲执事
            //1.加载注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            
            //2.获取数据库连接对象
            Connection conne = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root");
            
            //3.创建语句
            Statement St = conne.createStatement();
            
            //4.执行SQL语句
            St.executeUpdate(sql);
            
            //5.释放空间(先开后关)
            St.close();
            conne.close();
        
        
    }
        /*
         * 删除数据SQL：delete from t_student where id = 4
         * 
         */
        
        @Test
        public void testDelete() throws Exception {
            String sql = "delete from t_student where id = 4";
            //贾琏欲执事
            //1.加载注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            
            //2.获取数据库连接对象
            Connection conne = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root");
            
            //3.创建语句
            Statement St = conne.createStatement();
            
            //4.执行SQL语句
            St.executeUpdate(sql);
            
            //5.释放空间(先开后关)
            St.close();
            conne.close();
        
        
    }
    
}

DQL查询操作

多行查询

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;


public class DQLTest {
    /*
     * 多行查询 SQL：select * from t_student
     * 
     */
    
    @Test
    public void testList() throws Exception {
        //1.加载注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        
        //2.获取数据库连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root");
        
        
        //3.创建语句对象
        Statement st = conn.createStatement();
        
        //4.执行SQL语句
        
        String sql = "select * from t_student";
        ResultSet rs = st.executeQuery(sql);
        
        //申明一个list集合，封装一个个学生对象
        List<Student> students = new ArrayList<>();
        
        //循环遍历
        while (rs.next()) {
            //通过表中的数据获取对应java数据的，从1开始
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            //将每一行数据封装成一个个student对象
            Student stu = new Student(id, name, age);
            System.err.println(stu);
        }
        

        //5.释放资源
        rs.close();
        st.close();
        conn.close();
        
    }
    
}

单行查询：