首先导入包 构架包
#增加语句 INSERT INTO `studentdb`.`tb_students`(`name`, `age`) VALUES ('礼物', 12); INSERT INTO `studentdb`.`tb_student`(`name`, `age`) VALUES ('hellows', 22); #修改数据 age ---14 UPDATE `studentdb`.`tb_student` SET `age` = 14 WHERE ` id` = 2 #删除 DELETE FROM `studentdb`.`tb_student` WHERE ` id` = 3 #查询sql语句 SELECT * FROM tb_students SELECT * FROM tb_student WHERE ` id` = 1 SELECT *FROM tb_student WHERE name='张三'and age='20' DESC tb_student
第一部分回顾基本的mysql语句。
INSERT INTO `studentdb`.`tb_students`(`name`, `age`) VALUES ('礼物', 12); INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1013, 'Html5', 'hellowhtml5', '200元', 678, '2020/1/2', '你哈')
@1查表的结构 DESC 表的名称
DESC book
@2增 加一条语句内容
INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (10015, '我是增加的语句内容', 'hellow', '45', 234, '2020/1/2', 'nme')
INSERT INTO `studentdb`.`tb_students`(`name`, `age`) VALUES ('礼物', 12); INSERT INTO `studentdb`.`tb_student`(`name`, `age`) VALUES ('hellows', 22); INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('Sping', '中国邮政', '400元', 456, '2021/11/30', 'spingsping') INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (NULL, 'jquery', 'sum', '123', 345, '2020/1/2', 'gh') INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1013, 'Html5', 'hellowhtml5', '200元', 678, '2020/1/2', '你哈')
@3删 DELETE FROM `studentdb`.`book` WHERE `id` = 1015 AND `name` = Cast('Html5' AS Binary(5))
DELETE FROM `studentdb`.`book` WHERE `id` = 1015 AND `name` = Cast('Html5' AS Binary(5)) DELETE FROM `studentdb`.book WHERE ` id` = 1007
@4改 UPDATE `studentdb`.`book` SET `name` = 'php', `maker` = 'as', `price` = '344元', `num` = 2334, `time` = '2021/2/4', `autor` = 'nees' WHERE `id` = 10014 AND `name` = Cast('ps' AS Binary(2))
UPDATE `studentdb`.`book` SET `name` = '我是修改后的数据内容', `maker` = 'woshixougaihodeshujunr', `price` = '345' WHERE `id` = 10014 AND `name` = Cast('php' AS Binary(3))
@5查 找一条记录 和 多条记录内容。
SELECT *FROM book WHERE name='Sping'and maker='中国邮政' SELECT *FROM book WHERE price='400元'or price='500元' SELECT * FROM book
完整的sql语句内容
#增加一条数据 INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('Sping', '中国邮政', '500元', 456, '2021/11/30', 'spingsping') #查询表单结构 DESC book #查询表的内容 SELECT * FROM book #查询一条记录 SELECT *FROM book WHERE name='Sping'and maker='中国邮政' #查询多个条件 #且 and #或 or INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('Sping', '中国邮政', '400元', 456, '2021/11/30', 'spingsping') SELECT *FROM book WHERE price='400元'or price='500元' #修改数据内容 UPDATE `studentdb`.`book` SET `maker` = 'kut' WHERE `id` = 1005 #修改大量内容 SELECT * FROM book #创建一个数据 INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('SpingBoot', '中国人名出版社', '300元', 356, '2021/12/30', 'spingbook') #修改大量的数据内容 INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (NULL, 'jquery', 'sum', '123', 345, '2020/1/2', 'gh') UPDATE `studentdb`.`book` SET `name` = 'html', `maker` = 'sumdt', `price` = '678', `num` = 340, `time` = '2021/1/2', `autor` = 'ghj' WHERE `id` = 1008 #删除表的数据 DELETE FROM `studentdb`.book WHERE ` id` = 1007 DELETE FROM `studentdb`.`book` WHERE `id` = 1004 -- 练习题 #增一条语句 INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1013, 'Html5', 'hellowhtml5', '200元', 678, '2020/1/2', '你哈') #改一条语句的多个条件 UPDATE `studentdb`.`book` SET `name` = 'php', `maker` = 'as', `price` = '344元', `num` = 2334, `time` = '2021/2/4', `autor` = 'nees' WHERE `id` = 10014 AND `name` = Cast('ps' AS Binary(2)) #查表的结构 DESC book DESC tb_student #查表的内容 SELECT * FROM book #删除语句 INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1015, 'Html5', 'maysquery', '210元', 678, '2020/11/30', '增加的一条语句') #删除上面增加的语句 DELETE FROM `studentdb`.`book` WHERE `id` = 1015 AND `name` = Cast('Html5' AS Binary(5)) UPDATE `studentdb`.`book` SET `maker` = '', `price` = '' WHERE `id` = 10014 AND `name` = Cast('php' AS Binary(3))
第二部分用java的俩种模式开发模式
这是第二种方案示意图
@6第一种方案。
package jdbc; public class Student { private int stuId; private String stuName; private String stuSex; private int stuAge; public Student() { super(); // TODO Auto-generated constructor stub } public Student(int stuId, String stuName, String stuSex, int stuAge) { super(); this.stuId = stuId; this.stuName = stuName; this.stuSex = stuSex; this.stuAge = stuAge; } /** * @return the stuId */ public int getStuId() { return stuId; } /** * @param stuId the stuId to set */ public void setStuId(int stuId) { this.stuId = stuId; } /** * @return the stuName */ public String getStuName() { return stuName; } /** * @param stuName the stuName to set */ public void setStuName(String stuName) { this.stuName = stuName; } /** * @return the stuAge */ public int getStuAge() { return stuAge; } /** * @param stuAge the stuAge to set */ public void setStuAge(int stuAge) { this.stuAge = stuAge; } @Override public String toString() { return "Student [stuId=" + stuId + ", stuName=" + stuName + ", stuSex=" + stuSex + ", stuAge=" + stuAge + "]"; } }
java基础一键生成
第二步导入架构包
第三步建立数据库的连接将数据库的打开与关闭。建立在一个类中。
package jdbc; //方案一 //构建方法体 //建立链接数据库 关闭数据库 import java.sql.*; public class DBUtil { /** * 建立数据库连接 */ //fist public static Connection getConnection() { Connection conn=null; try { Class.forName("com.mysql.cj.jdbc.Driver"); System.out.println("加载驱动类成功"); //建立数据库 String url ="jdbc:mysql://127.0.0.1:3306/students"; System.out.println("mysql连接成功"); String username="root"; String password="123456"; conn=DriverManager.getConnection(url,username,password); } catch (Exception e) { e.printStackTrace(); System.out.println("数据库连接成功，开始了"); } return conn; } //last //封装方法 public static void getClose(Connection conn) { try { if(conn!=null) { conn.close(); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } System.err.println("数据库关闭成功"); } }
第四步@1进入数据的增加一条记录的操作：
package jdbc; import java.sql.*; import java.sql.Connection; import java.sql.DriverManager; //增加信息 public class JDBCZeng { public static void main(String[] args) throws Exception{ Connection conn=DBUtil.getConnection(); System.out.println(conn); //获取 statement对象 Statement stmt =conn.createStatement(); //增加 String sql=" INSERT INTO `students`.`student`(`stuId`, `stuName`, `stuSex`, `stuAge`) VALUES (1008, 'hjy', '女', 35)"; //4 执行语句 int count= stmt.executeUpdate(sql); if(count>0) { System.out.println("学生插入成功"); }else { System.out.println("in the end"); } //5 int the end //conn.close(); DBUtil.getClose(conn); } }
@2进入数据的修改操作。
package jdbc; import java.sql.*; /** * 修改数据信息用用 * @author MZFAITHDREAM * */ public class JDBC修改 { //修改信息 public static void main(String[] args) throws Exception { // 建立链接 Connection conn = DBUtil.getConnection(); // Statement stmt =conn.createStatement(); String sql="update student set stuSex='男' "; stmt.executeLargeUpdate(sql); DBUtil.getClose(conn); } }
@3查找数据库的记录
@4删除内容
package jdbc; import java.sql.*; //查询学生信息 //删除信息 public class JDBCDelect { public static void main(String[] args) throws Exception { // 建立链接 Connection conn = DBUtil.getConnection(); //创建statement对象 Statement stmt =conn.createStatement(); //执行mysql语句 String sql="DELETE FROM `students`.`student` WHERE `stuId` = 1006"; ResultSet rs =stmt.executeQuery(sql); } }
第二种方案。
第一步导入数据库的构架包内容。
跟上面的内容一样。
package com.db.text; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.junit.Test; public class JDBC1 { public Connection con=null; /** * 定义方法，用于连接数据库 */ public void getConnection() { //1.加载插件 try { Class.forName("com.mysql.jdbc.Driver"); //2.准备连接数据的信息:要连接的数据库的地址 用户名 密码 String url="jdbc:mysql://localhost:3306/studentdb?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimeZone=UTC"; String username="root"; String password="123456"; //使用设备管理器类根据提供的信息连接数据库 con=DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } } @Test public void test1() { //添加数据 getConnection(); //判断是否连接成功 if (con!=null) { //准备要执行的添加sql语句 //在jdbc连接数据库中，使用占位符? String sql="insert into tb_students(name,age) VALUES(?,?)"; //准备执行sql语句 try { PreparedStatement ps=con.prepareStatement(sql); ps.setString(1, "你好 mysql"); ps.setInt(2, 22); int count=ps.executeUpdate(); if (count>0) { System.out.println("添加成功"); } //执行完成之后，要断开数据库连接 ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Test public void test2() { //修改语句 getConnection(); if (con!=null) { String sql="update tb_students set age=? where id=?"; try { PreparedStatement ps=con.prepareStatement(sql); ps.setInt(1, 15); ps.setInt(2, 1); int i=ps.executeUpdate(); if (i>0) { System.out.println("修改成功"); } ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Test public void test3() { //删除语句 getConnection(); if (con!=null) { String sql="delete from tb_students where id=?"; try { PreparedStatement ps=con.prepareStatement(sql); ps.setInt(1, 5); int i=ps.executeUpdate(); if (i>0) { System.out.println("删除成功"); } ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Test public void test4() { //查询语句 //根据id=1查询学生的信息 getConnection(); if (con!=null) { String sql="select * from tb_students where id=?"; try { PreparedStatement ps=con.prepareStatement(sql); ps.setInt(1, 1); ResultSet rs=ps.executeQuery(); //因为查询出来的数据是一张表的结构，游标默认停留在第一行，是字段名 //所有需要获得的数据要从第二行开始 if (rs.next()) { int id=rs.getInt(1); String name=rs.getString(2); int age=rs.getInt(3); System.out.println(id+"--"+name+"--"+age); } //断开连接 rs.close(); ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Test public void test5() { List<Map<String, Object>> oList=new ArrayList<Map<String,Object>>(); //查询多行数据 getConnection(); if (con!=null) { String sql="select * from tb_student"; try { PreparedStatement ps=con.prepareStatement(sql); ResultSet rs=ps.executeQuery(); while (rs.next()) { int id=rs.getInt(1);//4 String name=rs.getString(2);//王六 int age=rs.getInt(3);//25 Map<String, Object> oMap=new HashMap<String, Object>(); oMap.put("id", id); oMap.put("name", name); oMap.put("age", age); oList.add(oMap); } //断开连接 rs.close(); ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } //循环遍历集合，显示所有数据 for (Map<String, Object> map : oList) { System.out.println(map); } //jdk1.8的新特性 List<String> o=new ArrayList<String>(); o.add("abc"); o.add("123"); o.add("456"); o.add("789"); //labdam表达式 o.forEach(s->System.out.println(s)); } }
第二种方案的增删改查操作内容。
package com.db.text; import java.util.ArrayList; import java.util.List; import org.junit.Test; public class JDBC { @Test public void test1() { List<String> o =new ArrayList<String>(); o.add("hellow world"); o.add("adfbc"); o.add("abffc"); //拉马 labdam 表达式 o.forEach(s->System.out.println(s)); } }
