JavaSE-11连接MySql-8.0.16的连接步骤
服务以及属性
这两项为本人测试几次后终于连接数据库的配置，服务要启动，属性的登陆设置为本地登录（仅供参考）
创建java工程并导入jar包并buildPath
本人使用的是C:\Program Files (x86)\MySQL\Connector J 8.0下的mysql-connector-java-8.0.16.jar，正是安装mysql里的jar包
打开sqlyog图形工具并登陆用户login
本人是把全局特权和对象级别特权下需要使用的数据库全部勾选上，对象级别特权不勾选也是可以的
创建连接类JDBCDemo01
注意这个是Class.forName(“com.mysql.cj.jdbc.Driver”);，Class.forName(“com.mysql.jdbc.Driver”);会报错
package com.sikiedu.jdbc01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo01 {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");//使用驱动连接数据库
//String url="jdbc:mysql://localhost:3306/test";
String url="jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT";//设置编码格式
String user="login";
String password="123";
Connection con=DriverManager.getConnection(url, user, password);
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from customers");
while(rs.next()) {
rs.getInt(1);
rs.getString(2);
System.out.println(rs.getInt(1)+","+rs.getString(2));
}
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(con!=null) con.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
成功显示数据
在连接数据库前出现的问题
①连接数据库报错 The server time zone value ‘?й???’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
本人参考CSDN上的解决方案----在jdbc连接的url后面加上serverTimezone=GMT即可解决问题 成功连接上了
②控制台报错 Caused by: java.sql.SQLException: Access denied for user ‘AzurLane’@‘localhost’ (using password: YES)
AzurLane是本人的一个登陆用户，我的解决方案是把这个用户直接删除，然后创建一个新的用户login就神奇的连入数据库了
PreparedStatement防止sql注入
PreparedStatement 是先传入sql语句再设置sql语句中的？值最后调用executeQuery执行(不需要传入sql)
public static void main(String[] args) {
// System.out.println(selectByUsernamePassword("xuefeng", "123"));
// System.out.println(selectByUsernamePassword("ssddw", " asdw 'or' 1=1"));//true sql注入
System.out.println(selectByUP2("xuefeng", "123"));
System.out.println(selectByUP2("ssddw", " asdw 'or' 1=1"));//false Preparement 防止sql注入
}
public static boolean selectByUsernamePassword(String username,String password) {
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT";
try {
con=DriverManager.getConnection(url,"login","123");
stmt=con.createStatement();
String sql="select * from user where username= '"+username+"' and password = '"+password+"'";
rs=stmt.executeQuery(sql);
if(rs.next()) {
return true;
}else {
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null) rs.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(stmt!=null) stmt.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(con!=null) con.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
return false;
}
public static boolean selectByUP2(String username,String password) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
//PreparedStatement 是先传入sql语句再设置sql语句中的？然后调用executeQuery执行(不需要传入sql)
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT";
try {
con=DriverManager.getConnection(url,"login","123");
String sql="select * from user where username=? and password=?";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
rs=pstmt.executeQuery();
if(rs.next()) {
return true;
}else {
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null) rs.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(pstmt!=null) pstmt.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(con!=null) con.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
return false;
}
分页查询
limit [位置偏移量,]行数位置偏移量是从，哪一行开始（行数从0开始），行数是指查询几行
如果要查询 第7页，每页8行
起始和末尾行数
0-7 第一页
8-15 第二页
16-23 第三页
…
起始行数为 （页数-1）*8
//pageNumber是页数 第几页 pageCount是每页显示多少条数据
public static void selectUserByPage(int pageNumber,int pageCount) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");//使用驱动连接数据库
//String url="jdbc:mysql://localhost:3306/test";
String url="jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT"; String user="login";
String password="123";
con=DriverManager.getConnection(url, user, password);
pstmt=con.prepareStatement("select * from user limit ?,?");
pstmt.setInt(1, (pageNumber -1) *pageCount);
pstmt.setInt(2, pageCount);
rs=pstmt.executeQuery();
while(rs.next()) {
rs.getInt(1);
rs.getString(2);
//System.out.println(rs.getInt(1)+","+rs.getString(2));两种查询方式
System.out.println(rs.getInt("id")+","+rs.getString("username")+","+rs.getString("password"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null) rs.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(pstmt!=null) pstmt.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(con!=null) con.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
提取工具类JDBCUtils
package com.sikiedu.jdbc01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCUtils {
public static final String connectionURL="jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT";
private static final String username="login";
private static final String password="123";
public static Connection getConnection() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
try {
return DriverManager.getConnection(connectionURL,username,password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
};
public static void close(ResultSet rs,PreparedStatement pstmt,Connection con ) {
try {
if(rs!=null) rs.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(pstmt!=null) pstmt.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
if(con!=null) con.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
插入，修改和删除操作
//crud create read update delete
public static void insert(String username,String password) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="insert into user(username,password) values(?,?)";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
int result=pstmt.executeUpdate();//返回值代表受到影响的行数
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.close(rs, pstmt, con);
}
}
//通过用户名查找id
public static int ByUsernameToId(String username) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="select id from user where username=?";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, username);
rs=pstmt.executeQuery();
if(rs.next()) {
rs.getInt(1);
// System.out.println("id:"+rs.getInt(1));
return rs.getInt(1);
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.close(rs, pstmt, con);
}
return 0;
}
public static void delete(int id) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="delete from user where id=?";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, id);
int result=pstmt.executeUpdate();//返回值代表受到影响的行数
if(result>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.close(rs, pstmt, con);
}
}
public static void update(int id,String newPassword) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="update user set password =? where id=?";
pstmt=con.prepareStatement(sql);
pstmt.setString(1,newPassword);
pstmt.setInt(2, id);
int result=pstmt.executeUpdate();//返回值代表受到影响的行数
if(result>0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.close(rs, pstmt, con);
}
}
事务
//事务
// 余额 a b
//放在一个事务里面
// a -1000
//断电 数据库崩溃
// b +1000
public static void transferAccounts(String username1,String username2,int money) {
Connection con = null;
PreparedStatement stmt1 = null;
PreparedStatement stmt2 = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
con.setAutoCommit(false);//开启事务
String sql = "update user set balance = balance - ? where username = ?";
stmt1 = con.prepareStatement(sql);
stmt1.setInt(1, money);
stmt1.setString(2, username1);
stmt1.executeUpdate();// 返回值代表受到影响的行数
// String s = null;
// s.charAt(2);//隐式空指针异常为了测试事务的回滚
sql = "update user set balance = balance + ? where username = ?";
stmt2 = con.prepareStatement(sql);
stmt2.setInt(1, money);
stmt2.setString(2, username2);
stmt2.executeUpdate();// 返回值代表受到影响的行数
con.commit();//提交事务
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(stmt2, stmt1, con);
}
}
Connection连接池
在JDBCUtils中创建一个简单的连接池
private static ArrayList<Connection> conList=new ArrayList<Connection>();
static {
for(int i=0;i<5;i++) {
Connection con=createConnection();
conList.add(con);
}
}
public static Connection getConnection() {
if(conList.isEmpty()==false) {
Connection con=conList.get(0);
conList.remove(con);
return con;
}else {
return createConnection();//连接池中Connection使用完后再创建
}
}
private static Connection createConnection() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
try {
return DriverManager.getConnection(connectionURL,username,password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
};
private static void closeResultSet(ResultSet rs) {
try {
if(rs!=null) rs.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
private static void closePreparedStatement(PreparedStatement pstmt) {
try {
if(pstmt!=null) pstmt.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
private static void closeConnection(Connection con) {
conList.add(con);//把用完的connection还给连接池
}
dbcp连接池
dbcp必须引入commons-dbcp2-2.7.0.jar和mysql-connector-java-8.0.16.jar好像需要引入commons-pool2-2.8.0.jar，但是我两个commons包都引入并都buildPath了但是还是报错
Exception in thread “main” java.lang.NoClassDefFoundError: Could not initialize class com.sikiedu.jdbc01.DBCPDataSource
好像是还有jar包没引入，等待日后解决了
DBCPDataSource.java
package com.sikiedu.jdbc01;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp2.BasicDataSource;
public class DBCPDataSource {
public static final String connectionURL="jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT";
private static final String username="login";
private static final String password="123";
private static BasicDataSource ds;
static {
//初始化dbcp数据源
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl(connectionURL);
ds.setUsername(username);
ds.setPassword(password);
ds.setInitialSize(5);
ds.setMaxTotal(20);
ds.setMinIdle(3);
}
public static Connection getConnection() {
try {
return ds.getConnection();//通过dbcp得到的链接，不需要归还，直接close就行
}catch(SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,PreparedStatement pstmt,Connection con ) {
closeResultSet(rs);
closePreparedStatement(pstmt);
closeConnection(con);
}
public static void close(PreparedStatement pstmt1,PreparedStatement pstmt2,Connection con) {
closePreparedStatement(pstmt1);
closePreparedStatement(pstmt2);
closeConnection(con);
}//这个方法是 前面事务需要使用的方法
private static void closeResultSet(ResultSet rs) {
try {
if(rs!=null) rs.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
private static void closePreparedStatement(PreparedStatement pstmt) {
try {
if(pstmt!=null) pstmt.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
private static void closeConnection(Connection con) {
try {
if(con!=null) con.close(); //这里的close是指把connection归还给dbcp连接池而不是关闭
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
调用dbcp连接池举例
//通过用户名查找id
public static int ByUsernameToId(String username) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=DBCPDataSource.getConnection();
String sql="select id from user where username=?";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, username);
rs=pstmt.executeQuery();
if(rs.next()) {
rs.getInt(1);
return rs.getInt(1);
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBCPDataSource.close(rs, pstmt, con);
}
return 0;
}
C3P0连接池
注意：使用C3P0连接池需要导入c3p0-0.9.5.5.jar和mchange-commons-java-0.2.19.jar和mysql-connector-java-8.0.16.jar三个jar包并BuildPath
C3P0本人成功连接上了
C3P0DataSource
package com.sikiedu.jdbc01;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0DataSource {
public static final String connectionURL="jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT";
private static final String username="login";
private static final String password="123";
private static ComboPooledDataSource ds;
static {
try {
ds=new ComboPooledDataSource();
ds.setDriverClass("com.mysql.cj.jdbc.Driver");
ds.setJdbcUrl(connectionURL);
ds.setUser(username);
ds.setPassword(password);
ds.setInitialPoolSize(5);
ds.setMaxPoolSize(20);
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,PreparedStatement pstmt,Connection con ) {
closeResultSet(rs);
closePreparedStatement(pstmt);
closeConnection(con);
}
private static void closeResultSet(ResultSet rs) {
try {
if(rs!=null) rs.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
private static void closePreparedStatement(PreparedStatement pstmt) {
try {
if(pstmt!=null) pstmt.close();
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
private static void closeConnection(Connection con) {
try {
if(con!=null) con.close(); //这里的close是指把connection归还给c3p0连接池而不是关闭
}catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
调用C3P0连接池举例
//通过用户名查找id
public static int ByUsernameToId(String username) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=C3P0DataSource.getConnection();
String sql="select id from user where username=?";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, username);
rs=pstmt.executeQuery();
if(rs.next()) {
rs.getInt(1);
return rs.getInt(1);
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
C3P0DataSource.close(rs, pstmt, con);
}
return 0;
}
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
