9、数据库设计
9.1、为什么需要设计
9.2、三大范式
10、JDBC（重点）：用驱动去连数据库的
10.3、第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
- 在idea中创建一个项目
- 导入数据库驱动（导入一个jar包,在chrome里面）
新建一个lib包
导入jar包
导入完了之后还要单击lib右键，选择Add as library,然后点击OK - 编写测试代码
package com.kuang.lesson01;
import java.sql.*;
//我的第一个JDBC程序
public class jdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法，加载驱动
//2.用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "wh123456";
//3.连接成功，返回一个数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL对象 Statement 执行sql对象
Statement statement = connection.createStatement();
//5.执行SQL的对象，去执行SQL 可能存在结果，查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,结果集中封装了全部的查询结果
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("pwd="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birth="+resultSet.getObject("birthday"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
id=1
name=zhansan
pwd=123456
email=zs@sina.com
birth=1980-12-04
id=2
name=lisi
pwd=123456
email=lisi@sina.com
birth=1981-12-04
id=3
name=wangwu
pwd=123456
email=wangwu@sina.com
birth=1979-12-04
Process finished with exit code 0
JDBC中对象解释
statement对象详解
建一个文件夹放加载信息（平时会把配置类单独放出来，比如下面连接JDBC的信息）文件夹名字：db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=wh123456
//有些对象我们只需要用一次，核心变的只有Statement对象进行增删改查，所以把不需要改变的单独放在一个工具类里面实现。工具类名字jdbcUtils
package com.kuang.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//有些对象我们只需要用一次，核心变的只有Statement对象进行增删改查，所以把不需要改变的单独放在一个工具类里面实现
public class jdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream resourceAsStream = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
//1.驱动器只加载一次，所以放在静态代码块里面
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接资源
public static void release(Connection con, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con!=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
在IDEA里面控制数据库的增删改查
插入
package com.kuang.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
//因为返回的对象需要释放，所以提了出来Connection con = null;
con = jdbcUtils.getConnection(); //获取数据库连接
st = con.createStatement(); //获取SQL执行对象
String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
"VALUES(5,'xiuxiu','123456','xiuxiu@sina.com','1995-12-04')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
jdbcUtils.release(con,st,rs);
}
}
}
查询
package com.kuang.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = jdbcUtils.getConnection();
st = con.createStatement();
String sql = "SELECT * FROM users where id = 1";
rs = st.executeQuery(sql); //查询完会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
Statement对象方法会产生SQL注入问题
PraparedStatement对象可以解决SQL注入问题
其他工具类和配置文件和Statement还是一样的
插入
package com.kuang.lesson03;
import com.kuang.lesson02.utils.jdbcUtils;
import java.util.Date;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null; //区别
try {
con = jdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = con.prepareStatement(sql); //预编译SQL，先写sql,然后不执行
//手动给参数赋值
st.setInt(1,6);
st.setString(2,"qinjiang");
st.setString(3,"1232112");
st.setString(4,"2473467@qq.com");
//注意点：sql.Date是数据库的 java.sql.Date()
// util.Date是Java的 new Date().getTime() 获得时间戳 计算机的时间
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
jdbcUtils.release(con,st,null);
}
}
}
删除
package com.kuang.lesson03;
import com.kuang.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestDelete {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
try {
con = jdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "delete from users where id = ?";
st = con.prepareStatement(sql); //预编译SQL，先写sql,然后不执行
//手动给参数赋值
st.setInt(1,5);
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("删除成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
jdbcUtils.release(con,st,null);
}
}
}
更新
package com.kuang.lesson03;
import com.kuang.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.*;
public class TestUpdate {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
try {
con = jdbcUtils.getConnection();
//区别
//使用?占位符代替参数，即想要传入的值
String sql = "update users set `NAME`=? where id=?;";
st = con.prepareStatement(sql); //预编译SQL，先写sql,然后不执行
//手动给参数赋值
st.setString(1,"狂神");
st.setInt(2,2);
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("更新成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
jdbcUtils.release(con,st,null);
}
}
}
查询（只有查询与增删改有区别）
package com.kuang.lesson03;
import com.kuang.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = jdbcUtils.getConnection();
String sql = "select * from users where id=?"; //编写sql
st = con.prepareStatement(sql); //预编译
st.setInt(1,1); //传递参数;
rs = st.executeQuery(); //执行
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
jdbcUtils.release(con,st,rs);
}
}
}
10.7、使用IDEA连接数据库
扫一扫
1555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
