这里写目录标题
-
- 环境
- 一.不用工具类操作数据库
- 二.用工具类操作数据库
- 三.用工具类的登录demo
环境
1.导入jar包
2.写配置文件并标记成类路径
jdbc.properties
jdbc.properties:
jdbc.username=root
jdbc.password=root
jdbc.url=jdbc:mysql://localhost:3306/day20
jdbc.table="user"
一.不用工具类操作数据库
demo1.java
public class demo1{
//查
@Test
public void test{
//配置文件
Properties properties = new Properties();
//当当前类加载时就获得类路径下配置文件的流,前提是配置资源在配置类路径下 mark as resources root
InputStream ip=jdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
//properties 加载配置文件
properties.load(ip);
} catch (IOException e) {
e.printStackTrace();
}
username = properties.getProperty("jdbc.username");
password = properties.getProperty("jdbc.password");
url = properties.getProperty("jdbc.url");
table=properties.getProperty("jdbc.table");
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得连接
Connection connection=DriverManager.getConnection(url,userName,passWord);
//3.创建预编译sql对象
PreparedStatement pstm= connection.prepareStatement(sql);
//4.执行sql语句
String sql="select * from user ";
//返回集
ResultSet resultSet =pstm.executeQuery(sql);
List<User> list=new ArrayList<>();
//遍历类型于迭代器的返回集
while (resultSet.next())
{
User user =new User();
// User user = new User(resultSet.getInt("id"),//没拿到值
user.setId(resultSet.getInt("id"));//设置到了值
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setNickname(resultSet.getString("nickname"));
list.add(user);
/*System.out.print(resultSet.getObject(1)+" ");
System.out.print(resultSet.getObject(2)+" ");
System.out.print(resultSet.getObject(3)+" ");
System.out.println(resultSet.getObject(4));*/
System.out.println("------------------------------------------------");
}
for (User user : list) {
System.out.println(user);
}
//5.释放资源
if(resultSet!=null)
{
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
}
//增
@Test
public void insert(){
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//配置文件也只用获取一次
Properties properties = new Properties();
//当当前类加载时就获得类路径下配置文件的流,前提是配置资源在配置类路径下 mark as resources root
InputStream ip=jdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
//properties 加载配置文件
properties.load(ip);
} catch (IOException e) {
e.printStackTrace();
}
username = properties.getProperty("jdbc.username");
password = properties.getProperty("jdbc.password");
url = properties.getProperty("jdbc.url");
table=properties.getProperty("jdbc.table");
//获取连接
DriverManager.getConnection(url,username,password);
//创建sql语句让执行对象执行;
String sql="insert into user values(null,'zl',?,'赵六')";
//3.创建sql语句对象
PreparedStatement pstm = connection.prepareStatement(sql);
pstm.set(1,"123456");
int rows = pstm.executeUpdate(sql);
System.out.println("受影响行数"+rows);
//5.释放资源
if(statement==null)
{
statement.close();
}
if(connection==null){
connection.close();
}
}
}
增删改类似,都是调用executeUpdate(sql)方法。
二.用工具类操作数据库
因为每次调用方法的步骤都类似,创建驱动,返回连接,执行sql。
频繁的创建驱动,创建连接很消耗资源。
一个项目应该只要一个驱动。
jdbc.utils工具类
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.time.Period;
import java.util.Properties;
/**
* @Author:ZZZ
* @Date: 2020/11/19 16:43
* @Version 1.0
*/
//jdbc工具类
public class jdbcUtils {
//静态代码块里加载配置文件时用到,以及后面的连接数据库用到
private static String username;
private static String password;
private static String url;
private static String table;
//驱动只用建立一次,所以在静态代码块里写好
static {
try {
Class.forName("com.mysql.jdbc.Driver" );
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//配置文件也只用获取一次
Properties properties = new Properties();
//当当前类加载时就获得类路径下配置文件的流,前提是配置资源在配置类路径下 mark as resources root
InputStream ip=jdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
//properties 加载配置文件
properties.load(ip);
} catch (IOException e) {
e.printStackTrace();
}
username = properties.getProperty("jdbc.username");
password = properties.getProperty("jdbc.password");
url = properties.getProperty("jdbc.url");
table=properties.getProperty("jdbc.table");
}
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url,username,password);
//不再写到preparedStatement是因为需要传递sql进来,和外面的执行一样就没必要写了
// PreparedStatement ps = connection.prepareStatement(sql);
return connection;
}
//统一释放资源 ,先加载的后释放 ,查询有结果集的多一个参数
public static void close(ResultSet re,Statement stmt, Connection conn) throws SQLException {
close(stmt,conn);
if(re!=null)
{
re.close();
}
}
//重载
public static void close(Statement stmt, Connection conn) throws SQLException {
if(stmt!=null)
{
stmt.close();
}
if(conn!=null)
{
conn.close();
}
}
//结果
public static void re(ResultSet resultSet) throws SQLException {
while (resultSet.next()) {
int id1 = resultSet.getInt("id");
String username = resultSet.getString("username");
String password = resultSet.getString("password");
String nickname = resultSet.getString("nickname");
System.out.println("id: "+id1+" username: "+username+" password: "+password+" nickName: "+nickname);
}
}
}
三.用工具类的登录demo
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* @Author:ZZZ
* @Date: 2020/11/19 17:45
* @Version 1.0
*/
public class 登录demo {
public static void main(String[] args) throws Exception{
Connection conn=jdbcUtils.getConnection();
String username="zzz";
String password="123456";
String sql="select * from user where username=? and password=?";
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setString(1,username);
pstm.setString(2,password);
ResultSet resultSet = pstm.executeQuery();
boolean next = resultSet.next();
if(next==true)
{
System.out.println("登录成功");
}
else
{
System.out.println("登录失败");
}
}
}