1.先在mysql创建需要的数据库和表
use book;
create table t_user(
`id`intprimary key auto_increment,
`username` varchar(20) not nullunique,
`password` varchar(32) not null,
`email` varchar(200)
);
insert into t_user(`username`,`password`,`email`) values(‘admin‘,‘admin‘,‘admin@atguigu.com‘);
select* from t_user;
2.编写与数据库表对应的javaBean对象
packagecom.atguigu.pojo;//数据库表对应的javaBean对象
public classUser {private intid;privateString username;privateString password;privateString email;publicUser() {}public User(intid, String username, String password, String email) {super();this.id =id;this.username =username;this.password =password;this.email =email;
}public intgetId() {returnid;
}public void setId(intid) {this.id =id;
}publicString getUsername() {returnusername;
}public voidsetUsername(String username) {this.username =username;
}publicString getPassword() {returnpassword;
}public voidsetPassword(String password) {this.password =password;
}publicString getEmail() {returnemail;
}public voidsetEmail(String email) {this.email =email;
}
@OverridepublicString toString() {return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email + "]";
}
}
View Code
3.编写工具类JdbcUtil
3.1先导入需要的jar包(数据库和连接池需要):
3.2、在src源码目录下编写jdbc.properties属性配置文件
initialSize=5
maxActive=10
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf8
username=root
password=123456
注意:
1.如果你在url这么没有添加“?characterEncoding=utf8”,有可能会出现以下错误:
Unknown initial character set index ‘255‘ received from server. Initial client character set can be forced via the ‘characterEncoding‘ property.
从服务器收到的未知初始字符集索引“255”。初始客户端字符集可以通过“字符编码”属性强制设置。
2、如果你连接的mysql数据库是8.0及以上的,driver要记得改为:‘com.mysql.cj.jdbc.Driver‘,否则会报错。详细看https://blog.csdn.net/superdangbo/article/details/78732700
3.3、编写数据库工具类JdbcUtil
Druid数据库连接池有两种使用方式:
1.直接设置数据源参数并建立连接池
2.Properties配置数据源,读取数据源并建立连接池
方式一:直接设置数据源参数并建立连接池
packagecom.atguigu.util;importjava.io.IOException;importjava.io.InputStream;importjava.sql.SQLException;importjava.util.Properties;importcom.alibaba.druid.pool.DruidDataSource;importcom.alibaba.druid.pool.DruidDataSourceFactory;importjava.sql.Connection;public classJDBCUtil {private staticDruidDataSource dataSource;static{try{
dataSource= newDruidDataSource();
dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf8");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setInitialSize(5);
dataSource.setMaxActive(10);
}catch(Exception e) {//TODO Auto-generated catch block
e.printStackTrace();
}
}/*** 获取数据库连接池中的连接
*@return如果返回null,说明连接失败,有值就是获取连接成功*/
public staticConnection getConnection() {
Connection conn=null;try{
conn=(Connection)dataSource.getConnection();
}catch(Exception e) {
e.printStackTrace();
}returnconn;
}/*** 关闭连接,放回数据库连接池
*@paramconn*/
public static voidclose(Connection conn) {if(conn!=null) {try{
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
方式二:Properties配置数据源,读取数据源并建立连接池(Properties文件看3.2)
packagecom.atguigu.util;importjava.io.IOException;importjava.io.InputStream;importjava.sql.SQLException;importjava.util.Properties;importcom.alibaba.druid.pool.DruidDataSource;importcom.alibaba.druid.pool.DruidDataSourceFactory;//import com.mysql.jdbc.Connection;
importjava.sql.Connection;public classJdbcUtil {private staticDruidDataSource dataSource;static{try{
Properties properties=newProperties();//读取jdbc.properties属性配置文件
InputStream is=JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");//从流中记载数据
properties.load(is);//创建数据库连接池
dataSource=(DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
}catch(Exception e) {//TODO Auto-generated catch block
e.printStackTrace();
}
}/*** 获取数据库连接池中的连接
*@return如果返回null,说明连接失败,有值就是获取连接成功*/
public staticConnection getConnection() {
Connection conn=null;try{
conn=(Connection) dataSource.getConnection();
}catch(Exception e) {
e.printStackTrace();
}returnconn;
}/*** 关闭连接,放回数据库连接池
*@paramconn*/
public static voidclose(Connection conn) {if(conn!=null) {try{
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
3.4、JdbcUtil测试
packagecom.atguigu.test;importorg.junit.Test;importcom.alibaba.druid.util.JdbcUtils;importcom.atguigu.util.JdbcUtil;//import com.mysql.jdbc.Connection;
importjava.sql.Connection;public classJdbcUtilsTest {public static voidmain(String []args) {
Connection connection=JdbcUtil.getConnection();
System.out.println(connection);
JdbcUtils.close(connection);
}
}
结果:
最后:我遇到的坑有无数个,我搞了一下午,才弄好,哭泣。。。。
1、如果你遇到错误:Cause: java.sql.SQLException: Unknown initial character set index ‘255‘ received from server.
2、如果遇到Client does not support authentication protocol requested by server; consider upgrading MySQL client这个错误,有可能需要把用户密码登录的加密规则还原成mysql_native_password这种加密方式。如何修改可以参考这篇文章:https://blog.csdn.net/numberseven7/article/details/99548745