学习JDBC
(1)直接使用JDBC对数据库进行操作:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbctest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
//获取数据库连接对象
Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/asd?serverTimezone=GMT%2B8&useSSL=false","root","");
//定义sql语句
String sql="update user set username='宋义进' where id=2";
//获取执行sql的对象
Statement stmt=conn.createStatement();
//执行sql,并接受返回结果
int count=stmt.executeUpdate(sql);
//处理结果
System.out.println(count);
//释放资源
stmt.close();
conn.close();
}
}
刚开始报错了,连接及关闭数据库时区错误提示:*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.
查了资料后,发现问题在于新版的Mysql中的时区默认设置与本地时区之间是不同的,因此会报错。由此,解决方法即为修改时区设置即可,可以在连接数据库的url的最后把时区设置成协调世界时UTC,即加入代码:
?serverTimezone=GMT%2B8&useSSL=false
把GMT时间加上8个小时就等于东八区的时间;就可以解决问题了。
还有一个错误,WrongArgumentException: Malformed database URL,failed to parse the main URL sections,出现这个异常的原因是新版8.0的MySQL驱动配置改变了。MySQL新版驱动配置为:
URL=jdbc:mysql://127.0.0.1:3306/mg?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=UTC
(2)
把数据库连接信息存入配置文件中:
url=jdbc:mysql://localhost:3306/asd?serverTimezone=GMT%2B8&useSSL=false
user=root
password=
driver=com.mysql.cj.jdbc.Driver
创建一个新类,读取配置文件数据,连接数据库:
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.net.URL;
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
//读取文件,只需要读取一次就可以拿到这些值
static{
try {
//创建Properties集合类
Properties pro=new Properties();
ClassLoader classLoader=JDBCUtils.class.getClassLoader();
URL res=classLoader.getResource("jdbc.properties");
String path=res.getPath();
System.out.println(path);
//加载文件
pro.load(new FileReader(path));
//获取数值
url=pro.getProperty("url");
user=pro.getProperty("user");
password=pro.getProperty("password");
driver=pro.getProperty("driver");
Class.forName(driver);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
//释放资源
public static void close(ResultSet rs, Statement stmt,Connection conn){
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
在主类中:定义sql,获取执行sql对象
import java.sql.*;
import java.util.Scanner;
public class jdbctest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
nner sc= new Scanner(System.in);
System.out.println("请输入用户名:");
String username=sc.nextLine();
System.out.println("请输入密码:");
String password=sc.nextLine();
boolean loginFlag=new jdbctest().login(username,password);
if(loginFlag){
System.out.println("登录成功!");
}
else {
System.out.println("登录失败!");
}
}
//登录方法
public boolean login(String username,String password){
if(username==null||password==null){
return false;
}
Connection conn=null;
Statement stmt =null;
ResultSet rs=null;
try {
conn =JDBCUtils.getConnection();
//定义sql
String sql ="select * from user where username='"+username+"' and password='"+password+"'";
//获取执行sql对象
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs,stmt,conn);
}
return false;
}
}
运行结果: