1.创建一张Student表,创建表DDL
1 import static org.junit.Assert.*; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.Statement; 6 7 import org.junit.Test; 8 9 /** 10 * 创建一个学生表, 11 * SQL语句:create table t_student(id int prinmary key auto_increment,name varchar(20),age int) 12 * 13 */ 14 15 public class DDLTest { 16 private static final String str = null; 17 18 @Test 19 public void testCreateTable() throws Exception { 20 //贾琏欲执事,贾--加(贾)载注册驱动,琏--获取数据库连(琏)接对象 ,创建语(欲)句,执(执)行SQL语句,释(事)放资源(先开后关) 21 String sql = "create table t_student (id int primary key auto_increment,name varchar(50),age int)"; 22 23 //1.加载注册驱动 24 Class.forName("com.mysql.jdbc.Driver"); 25 26 //2.获取数据库连接对象 27 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); 28 29 //3.创建语句 30 Statement st = conn.createStatement(); 31 32 //4.执行SQL语句 33 st.executeUpdate(sql); 34 35 36 //5.释放资源(先开后关) 37 st.close(); 38 conn.close(); 39 } 40 41 42 }
增删改DML:
1 import static org.junit.Assert.*; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.Statement; 6 7 import org.junit.Test; 8 //插入数据 SQL:insert into t_student (name,age) values('小乔',30) 9 public class DMLTest { 10 11 12 @Test 13 public void testInsert() throws Exception { 14 String sql = "insert into t_student (name, age) values('大桥',30)"; 15 //贾琏欲执事 16 //1.加载注册驱动 17 Class.forName("com.mysql.jdbc.Driver"); 18 19 //2.获取数据库连接对象 20 Connection conne = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); 21 22 //3.创建语句 23 Statement St = conne.createStatement(); 24 25 //4.执行SQL语句 26 St.executeUpdate(sql); 27 28 //5.释放空间(先开后关) 29 St.close(); 30 conne.close(); 31 } 32 /* 33 * 34 * 修改操作:update t_student set name = '貂蝉',age = 35 where id = 3 35 * 36 * 37 */ 38 @Test 39 public void testUpdate() throws Exception { 40 String sql = "update t_student set name = '貂蝉',age = 35 where id =3"; 41 //贾琏欲执事 42 //1.加载注册驱动 43 Class.forName("com.mysql.jdbc.Driver"); 44 45 //2.获取数据库连接对象 46 Connection conne = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); 47 48 //3.创建语句 49 Statement St = conne.createStatement(); 50 51 //4.执行SQL语句 52 St.executeUpdate(sql); 53 54 //5.释放空间(先开后关) 55 St.close(); 56 conne.close(); 57 58 59 } 60 /* 61 * 删除数据SQL:delete from t_student where id = 4 62 * 63 */ 64 65 @Test 66 public void testDelete() throws Exception { 67 String sql = "delete from t_student where id = 4"; 68 //贾琏欲执事 69 //1.加载注册驱动 70 Class.forName("com.mysql.jdbc.Driver"); 71 72 //2.获取数据库连接对象 73 Connection conne = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); 74 75 //3.创建语句 76 Statement St = conne.createStatement(); 77 78 //4.执行SQL语句 79 St.executeUpdate(sql); 80 81 //5.释放空间(先开后关) 82 St.close(); 83 conne.close(); 84 85 86 } 87 88 }
DQL查询操作
多行查询
1 import static org.junit.Assert.*; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.Statement; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import org.junit.Test; 11 12 13 public class DQLTest { 14 /* 15 * 多行查询 SQL:select * from t_student 16 * 17 */ 18 19 @Test 20 public void testList() throws Exception { 21 //1.加载注册驱动 22 Class.forName("com.mysql.jdbc.Driver"); 23 24 25 //2.获取数据库连接对象 26 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); 27 28 29 //3.创建语句对象 30 Statement st = conn.createStatement(); 31 32 //4.执行SQL语句 33 34 String sql = "select * from t_student"; 35 ResultSet rs = st.executeQuery(sql); 36 37 //申明一个list集合,封装一个个学生对象 38 List<Student> students = new ArrayList<>(); 39 40 //循环遍历 41 while (rs.next()) { 42 //通过表中的数据获取对应java数据的,从1开始 43 int id = rs.getInt("id"); 44 String name = rs.getString("name"); 45 int age = rs.getInt("age"); 46 //将每一行数据封装成一个个student对象 47 Student stu = new Student(id, name, age); 48 System.err.println(stu); 49 } 50 51 52 //5.释放资源 53 rs.close(); 54 st.close(); 55 conn.close(); 56 57 } 58 59 }
单行查询: