一、 Jdbc快速入门(七个基本步骤)
package cn.itcast.jdbc;
import java.util.*;
import java.sql.*;
public class JdbcDemo1 {
public static void main(String[] args) throws Exception{
//1.抛出类找不到的异常,注册数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root");
//3.定义sql语句
String sql = "update account set balance = 500 where id = 1";
//4.获取执行sql的对象 statement
Statement stmt = connection.createStatement();
//5.执行Sql
int count = stmt.executeUpdate(sql);
//6.处理结果
System.out.println(count);
//7.释放资源
stmt.close();
connection.close();
}
}
二、JDBCutils用户登录案例
1.在jdbc同级目录下创建一个新的目录,再在新的目录下创建JDBCUtuils.java文件,JDBCutil工具类代码
package cn.itcast.utils;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils
{
private static String url;
private static String user;
private static String password;
private static String driver;
/*
* 文件的读取,只需要读取一次即可拿到这些值。故使用静态代码块
* */
static
//读取文件资源
{
try {
//1.创建Properties集合类
Properties pro = new Properties();
//2.加载配置文件
pro.load(new FileReader("src\\jdbc.properties"));
//3.获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4.注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/*
* 获取连接
* @return连接对象
* */
public static Connection getConnection()throws SQLException{
return DriverManager.getConnection(url, user, password);
}
/*
* 释放资源
*
* */
public static void close(Statement stmt, Connection conn)
{
if(stmt != null) {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/*
* 释放资源
* */
public static void close(ResultSet rs,Statement stmt, Connection conn)
{
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
2.使用JDBCUtils完成用户登录验证
*(在IDEA中创建一个包,再在包下创建一个JdbcDemo2.java文件)
package cn.itcast.jdbc;
import cn.itcast.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/*
* 需求:;1.通过键盘录入用户名和密码
* 2.判断用户是否登录成功
* */
public class JdbcDemo2 {
public static void main(String[] args) {
//1.键盘录入,接收用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
//2.调用方法
boolean flag = new JdbcDemo2().login(username, password);
//3.判断结果,输出不同语句
if (flag) {
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;
//1.获取连接
try {
conn = JDBCUtils.getConnection();
//2.定义SQL
String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";
//3.获取执行SQL的对象
stmt = conn.createStatement();
//4.执行查询
rs = stmt.executeQuery(sql);
//5.判断
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, stmt, conn);
}
return false;
}
}
3.在src目录下创建配置文件jdbc.properties
(IDEA中点文件–new–resource bundle)
url=jdbc:mysql:///db1
user=root
password=root
driver=com.mysql.jdbc.Driver
4.利用preparestatemen解决SQL注入问题
/*
* preparestatemen登录方法
* */
public boolean login(String username, String password) {
if (username == null || password == null) {
return false;
}
//连接数据库判断是否登录成功
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
//1.获取连接
try {
conn = JDBCUtils.getConnection();
**//特别需要注意preparestatement**
**//2.定义SQL
String sql = "select * from user where username = ? and password = ?;
//3.获取执行SQL的对象
pstmt = conn.prepareStatement(sql);
//4.执行查询,不需要传递sql
rs = pstmt.executeQuery();**
//5.判断
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, pstmt, conn);
}
return false;
}
}