工作室学习笔记3
JDBC相关内容
有一说一,不难
就是最近心态怪怪的
配置JDBC
Java DataBase Connector
我使用的是Mysql数据库,以我为例子
- 首先你要下载和你的数据库版本,jdk,jre版本对应的JDBC
版本对应参考:Mysql官网上有写,给你导过去嗷
下下来,zip压缩包里有一个叫:
mysql-connector-java-8.0.11.jar
- 项目主文件下新建lib文件夹,把刚才那个东西复制到这儿来。
- 左上角文件(File),点开项目结构,Project Settings里点击模块(Module),中间点击依赖,右方点击加号:“jars或目录”,找到刚才的文件,加入进去,最后如图:
搞定。
配置好了
诸多问题很多地方能够查到,我还没遇到什么卡死人的问题,在配置上面。。。
- 补充:
小心数据库时区,编码问题,设置时记得加global。
1. 注册数据库驱动,并实行基本数据库操作
以下内容参照博客
通过java实现了
DELETE
INSERT
SELECT
UPDATE
测试基础:已导入数据库,名称为test,表名为“测试表”。
INSERT操作
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class LinkDatabaseInsert {
public static void main(String [] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8","root","yuaoyuao78028");
String sql="insert into 测试表 value(?,?,?)";
PreparedStatement statement=connection.prepareCall(sql);
statement.setInt(1,114);
statement.setString(2,"lbwnb");
statement.setInt(3,23);
int i=statement.executeUpdate();
System.out.println(i);
statement.close();
connection.close();
//结果记录:测试基本成功,字符串插入中文失败
//实现:插入操作
}
}
中文设置再说
UPDATE操作
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class LinkDatabaseUpdate {
public static void main(String []args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8","root","yuaoyuao78028");
String sql="update 测试表 set test_c1=? where test_c1=?";
PreparedStatement statement=connection.prepareCall(sql);
statement.setInt(1,22);
statement.setInt(2,11);
int i=statement.executeUpdate();
System.out.println(i);
statement.close();
connection.close();
//结果记录:基本成功,注意参数问题不要写错
//实现:数据更新
}
}
DELETE操作
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class LinkDatabaseDelete {
public static void main(String []args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8","root","yuaoyuao78028");
String sql="delete from 测试表 where test_c1=?";
PreparedStatement statement=connection.prepareCall(sql);
statement.setInt(1,22);
int i=statement.executeUpdate();
System.out.println(i);
statement.close();
connection.close();
//结果记录:成功,被我删完了
//实现:数据删除
}
}
SELECT操作
package JDBC;
import java.sql.*;
public class LinkDatabaseSelect {
public static void main(String []args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8","root","yuaoyuao78028");
String sql="select * from 测试表 where test_c2=?";
PreparedStatement statement=connection.prepareCall(sql);
statement.setString(1,"lbwnb");
ResultSet resultSet=statement.executeQuery();
while(resultSet.next()){
int test_c1=resultSet.getInt(1);
String test_c2=resultSet.getString("test_c2");
int test_c3=resultSet.getInt(3);
System.out.println(test_c1+"\t"+test_c2+"\t"+test_c3+"\t");
}
resultSet.close();
statement.close();
connection.close();
//结果记录:成功,此次select通过sout输出
//实现:数据提取
}
}
总结:
class.forName("com.mysql.jdbc.Driver");
注册数据库驱动Connnection
连接数据库
内容依次是:
jdbc:mysql://主机/数据库名?编码模式,用户名,密码
?为占行符
编码一般用
useUnicode=true&characterEncoding=utf-8
据说一般能解决中文编码问题,但是我不能PreparedStatement
通过?设置参数,然后用
set.Int/String()…设置?的内容,为了防止单引号双引号的交错使用导致错误。(不信你就用+和’,"来试试)executeQuery/executeUpdate
前者是执行SELECT命令,返回ResultSet;后者执行DELETE,UPDATE,INSERT命令。
2. 增删改查
具体实现到用户层时,就需要建立DAO(data access object)了。
此时此刻已经进入了MVC架构的实现了。
建立User对象类
承接接口Serializable,实现序列化
为什么?
看看这里
import java.io.Serializable;
public class UserBean implements Serializable {
private String username;
private String password;
private String id;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
}
建立对于User的操作
这就是在DAO层的东西了
UserDAO:
import java.sql.SQLException;
public interface UserDAO {
//实现:获取信息,增添信息,更改信息,删除信息
public UserBean getUserInfo(String username,String password) throws SQLException, ClassNotFoundException;
public int insertUser(String username,String password);
public int updateUser(String username,int id);
public int deleteUser(int id);
}
基本实现增删改查的操作接口
先前,我建立了一个类实现连接数据库
以下是不完全的
DBUtil:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String DRIVER="com.mysql.cj.jdbc.Driver";
private static final String URL="jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8";
private static final String USER="root";
private static final String PWD="yuaoyuao78028";
private static Connection connection=null;
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER);
connection= DriverManager.getConnection(URL,USER,PWD);
return connection;
}
}
实现内容:返回一个配置了驱动"com.mysql.cj.jdbc.Driver",URL默认接口3306,数据库名test…的一个Connection。
这样接下来,就可以增删改查了。
UserDAOImpl1:
import DAO.UserDAO;
import DBUtil.DBUtil;
import Entity.UserBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDAOImpl1 implements UserDAO {
@Override
public UserBean getUserInfo(String username, String password) throws SQLException, ClassNotFoundException {
//初始化User对象,返回User
UserBean User=null;
Connection connection=DBUtil.getConnection();
String sql="select * from users where username=? and password=?";
PreparedStatement preparedStatement=connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setString(2,username);
//执行sql操作,使用ResultSet来存储结果
ResultSet resultSet=preparedStatement.executeQuery();
//返回所有满足的User
while(resultSet.next()){
User=new UserBean();
User.setId(resultSet.getInt("id"));
User.setPassword(resultSet.getString("password"));
User.setUsername(resultSet.getString("username"));
return User;
}
//关闭
resultSet.close();
preparedStatement.close();
connection.close();
return User;
}
@Override
public int insertUser(String username, String password) throws SQLException, ClassNotFoundException {
Connection connection=DBUtil.getConnection();
String sql="insert into users values(default,?,?)";
PreparedStatement preparedStatement=connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
int i=preparedStatement.executeUpdate();
preparedStatement.close();
connection.close();
return i;
}
@Override
public int updateUser(String username, int id) throws SQLException, ClassNotFoundException {
Connection connection=DBUtil.getConnection();
String sql="update users set username=? where id=?";
PreparedStatement preparedStatement=connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setInt(2,id);
int i=preparedStatement.executeUpdate();
preparedStatement.close();
connection.close();
return i;
}
@Override
public int deleteUser(int id) throws SQLException, ClassNotFoundException {
Connection connection=DBUtil.getConnection();
String sql="delete from users where id=?";
PreparedStatement preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
int i=preparedStatement.executeUpdate();
preparedStatement.close();
connection.close();
return i;
}
}
稍作试验:
package Test;
import DAO.impl.UserDAOImpl1;
import java.sql.SQLException;
public class Test1 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
UserDAOImpl1 userDAOImpl1=new UserDAOImpl1();
System.out.println(userDAOImpl1.insertUser("Argon","31415926535"));
System.out.println(userDAOImpl1.insertUser("Athsus","123123456456"));
System.out.println(userDAOImpl1.getUserInfo("Argon","31415926535"));
System.out.println(userDAOImpl1.getUserInfo("Athsus","123123456456"));
System.out.println(userDAOImpl1.updateUser("Teledamaii",2));
System.out.println(userDAOImpl1.getUserInfo("Teledamaii","123123456456"));
System.out.println(userDAOImpl1.deleteUser(2));
System.out.println(userDAOImpl1.deleteUser(3));
System.out.println(userDAOImpl1.deleteUser(4));
System.out.println(userDAOImpl1.deleteUser(5));
}
}
总结
手打了这么多,基本总结就是,通过Connection连接数据库,配置sql的字符串,并且放入PreparedStatement中,配置后执行语句->executeUpdate(),按需操作后关闭,返回需要的值
- 查
输入用户名和密码
返回你的信息(UserBean类) - 增
输入用户名和密码,增加你的信息
注意id使用default
返回执行次数 - 改
输入要改的用户名和你的id,把这个id对应的名字改了
返回执行次数 - 删
删除你输入的id的所有信息
返回执行次数
DBUtil学习实现
实现内容:
- 封装数据库连接
- 封装PreparedStatement
- 封装关闭资源
- 封装DML
package DBUtil;
import java.sql.*;
public class DBUtil {
private static String DRIVER="com.mysql.cj.jdbc.Driver";
private static String URL="jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8";
private static String USER="root";
private static String PWD="yuaoyuao78028";
private static Connection connection=null;
static{
DRIVER="com.mysql.cj.jdbc.Driver";
URL="jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8";
USER="root";
PWD="yuaoyuao78028";
}
//连接数据库
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER);
connection= DriverManager.getConnection(URL,USER,PWD);
return connection;
}
//利用DBUtil封装PreparedStatement
public static PreparedStatement getPreparedStatement(String sql,Connection connection) throws SQLException {
PreparedStatement preparedStatement=null;
preparedStatement=connection.prepareStatement(sql);
return preparedStatement;
}
//给你关了!
public static void closeAll(ResultSet resultSet,Statement statement,Connection connection){
try{
resultSet.close();
}catch(Exception e){}
try{
statement.close();
}catch (Exception e){}
try{
statement.close();
}catch (Exception e){}
}
//封装DML,指对于参数设置方法的封装思想
public static int executeDML(String sql,Object...objects) throws SQLException, ClassNotFoundException {
Connection connection=getConnection();
PreparedStatement preparedStatement=DBUtil.getPreparedStatement(sql,connection);
try {
//不自动提交表数据!
connection.setAutoCommit(false);
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i + 1, objects[i]);
}
int i = preparedStatement.executeUpdate();
//手动提交
connection.commit();
return i;
}catch(Exception e){
try{
connection.rollback();
}catch (SQLException e1){
e1.printStackTrace();
}
}finally{
DBUtil.closeAll(null,preparedStatement,connection);
}
return -1;
}
}
于是,终于,我们通过DBUtil进行封装一部分功能后,得到了如下的操作:
UserDAOImpl2
package DAO.impl;
import DAO.UserDAO1;
import DBUtil.DBUtil;
import Entity.UserBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDAOImpl2 implements UserDAO1 {
//利用DBUtil地增删改查
//增
public int insertUser(String username,String password) throws SQLException, ClassNotFoundException {
return DBUtil.executeDML("insert into users values(default,?,?)",username,password);
}
//改
public int updateUser(String username,int id) throws SQLException, ClassNotFoundException {
return DBUtil.executeDML("update users set username=? where id=?",username,id);
}
//删
public int deleteUser(int id) throws SQLException, ClassNotFoundException {
return DBUtil.executeDML("delete from users where id=?",id);
}
//查,还是那么麻烦w
public UserBean getUserInfo(String username,String password) throws SQLException, ClassNotFoundException {
UserBean User=null;
Connection connection=DBUtil.getConnection();
ResultSet resultSet=null;
String sql="select * from users where username=? and password=?";
PreparedStatement preparedStatement=DBUtil.getPreparedStatement(sql,connection);
//在这里如果使用executeDML方法就输出不了得到的信息了
try {
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
User = new UserBean();
User.setId(resultSet.getInt("id"));
User.setUsername(resultSet.getString("username"));
User.setPassword(resultSet.getString("password"));
return User;
}
}catch(SQLException e){
e.printStackTrace();
}finally{
//必走!!!
DBUtil.closeAll(resultSet,preparedStatement,connection);
}
return User;
}
}
- 注:为了图方便,我会在整个方法前面加上throw …Exception,但是这样感觉写起来不怎么好,有时候try看起来又令人不适。
为了规范写法,摸索一段时间再看吧