driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/数据库?useUnicode=true&characterEncoding=utf8&useSSL=true" username=root password=root
引擎：InnoDB
字符集：utf8
核对：utf8_general_ci
show databases;--查看所有的数据库 use 数据库名 show tables;--查看数据库中所有的表 discribe 表名 ; --显示数据库中所有表的信息 create databases 数据库名; exit; --数据库的列类型
varchar 可变字符串
text 文本串 ：保存大文本
datetime YYYY-MM-DD HH:mm:ss
timestamp 时间戳，1970.1.1到现在的毫秒数
增删改查CRUD增删改：st.executeUpdate(sql)
查：st.executeQuery(sql)
Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
//String sql = "delete from user where id=1";
//String sql = "update user set name='' where name =''";
int num = st.executeUpdate(sql);
if(num>0){
sout("插入成功");
}
Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中。
}
JDBC
新建lib–导入Jar包，Add as Library…
com.mysql.cj.jdbc.Drive
com.mysql.jdbc.Driver
步骤总结:
1、加载驱动
2、连接数据库DriverManager
3、获得执行sq|的对象Statement
4、获得返回的结果集
5、释放连接
package com.liu.www;
import java.sql.*;
public class f01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.用户信息和Url 5X:useUnicode=true&characterEncoding=utf8&useSSL=true
//8x:
//String url = "jdbc:mysql://localhost:3306/nsd_ssm0818?useUnicode=true&characterEncoding=utf8&useSSL=true";
String url = "jdbc:mysql://localhost:3306/nsd_ssm0818?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
String username = "root";
String password = "root";
//3.连接成功，数据库对象 Connection代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
//4.执行SQL的对象Statement 执行sql的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象去执行SQL,可能存在结果，查看返回结果
String sql = "SELECt * FROM userinfo";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部查询出来的结果
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("username="+resultSet.getObject("username"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
提取工具类
1.db.properties
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/nsd_ssm0818?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true password=root username =root
2.utils
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 in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty(driver);
url = properties.getProperty(url);
username = properties.getProperty(username);
password = properties.getProperty(password);
//驱动只加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放
public static void release(Connection conn, Statement st, ResultSet rs) {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.测试
public class TsetInsert {
public static void main(String[] args){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获得数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO userinfo(id,username,userpwd,usertel,isdelete,imgsrc)n"+
"VALUES(36,'哈哈哈','123555','13256965896',null,null)";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
PreparedStatement
//防止SQL注入
public class T02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();//获得数据库连接
//区别
//使用？ 占位符代替参数
String sql = "INSERT INTO userinfo(id,username) VALUES(?,?)";
//手动给参数赋值
st.setInt(1, 36);
st.setString(2, "ming");
int i = st.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, null);
}
}
}
事务
public class T03 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null ;
try {
conn = JdbcUtils.getConnection();//获得数据库连接
//关闭数据库的自动提交，自动会开启事务
conn.setAutoCommit(false);//开启事务
String sql = "INSERT INTO userinfo(id,username) VALUES(?,?)";
st = conn.prepareStatement(sql);
st.executeUpdate();
//业务完毕，提交事务
conn.commit();
System.out.println("成功");
} catch (SQLException e) {
try {
conn.rollback();//如果失败则回滚事务
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, null);
}
}
}
数据库连接池-DHCP
commons-dbcp2-2.1.1
commons-pool2-2.4.2
#连接设置dbcpconfig driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/nsd_ssm0818?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=123456 # initialSize=10 #最大连接数量 maxActive=50 # maxIdle=20 # minIdle=5 # maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】 #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交（auto-commit）状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读（read-only）状态。 #如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix） defaultReadonly= #driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。 #可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
public class JdbcUtils_DBCP {
private static BasicDataSource dateSource = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式-->创建
dateSource = BasicDataSourceFactory.createDataSource(properties)
} catch (Exception e) {
e.printStackTrace();
}
}
//从数据源获取连接
public static Connection getConnection() throws SQLException {
return dateSource.getConnection();
}
//释放
public static void release(Connection conn, Statement st, ResultSet rs) {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
数据库连接池-C3P0
c3p0-0.9.5.5
mchange-commons-java-0.2.19
com.mysql.cj.jdbc.Driver jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=UTC root 123456 5 10 5 20
public class JdbcUtils_c3p0 {
private static ComboPooledDataSource dateSource = null;
static {
try {
//创建数据源 工厂模式-->创建
dateSource = new ComboPooledDataSource("MySQL");//配置文件写法
} catch (Exception e) {
e.printStackTrace();
}
}
//从数据源获取连接
public static Connection getConnection() throws SQLException {
return dateSource.getConnection();
}
//释放
public static void release(Connection conn, Statement st, ResultSet rs) {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
