首先导入包?构架包
#增加语句
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第一种方案。
定义一个 Student的标准类
该类包含无惨/有参/get/set/tostring
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);
}
}
@7第二种方案。
第一步导入数据库的构架包内容。
?第二步建立数据库的连接
?
?
?跟上面的内容一样。
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));
}
}