一JDBC 的含义:
JDBC是Java提供的一套内和接口
链接数据哭的一套规范
链接数据库是你要做什么? 使用这些类和接口
JDBC操作数据库的步骤:
1.注册驱动(加载驱动类) registerDriver(Driver driver) Driver是数据库厂商实现的
2.获取数据库连接对象 getConnection(String url, String user, String password);
---这里就相当于连接上了数据库
3.通过连接对象 获取Sql 语句的执行对象 createStatement()
创建一个 Statement 对象来将 SQL 语句发送到数据库。 获取 执行SQL语句的对象;
4.执行SQL语句 通过 Statement 对象 ;
executeQuery(String sql) 返回值为resultset DQL (select)
executeUpdate(String sql) 返回值为 int 执行 DDL DML
5.处理执行SQL后得到的结果集
6.释放资源(调用close()方法)
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//注册驱动
// 如果这么注册相当于注册了两遍
//DriverManager.registerDriver(new Driver());
//Driver类源码中静态代码块
//并且代码块中已经注册了一次
//该如何正确注册驱动? 利用反射注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接对象
//url 数据库的连接地址 jdbc:mysql://主机IP地址 :数据库端口号3306/数据库名
String url = "jdbc:mysql://localhost:3306/mysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
//通过连接对象 获取Sql 语句的执行对象
Statement statement = connection.createStatement();
//执行SQL语句
String sql = "insert into goods"
+"(sname,sprice,sdesc)"
+"values('手机',8000,'一塔手机')";
int executeUpdate = statement.executeUpdate(sql);
System.out.println(executeUpdate);
connection.close();
statement.close();
}
处理执行SQL后得到的结果集
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
//执行SQL语句
//String sql ="select * from goods";
String sql ="select sname,sid,sprice,sdesc from goods";
ResultSet executeQuery = statement.executeQuery(sql);
//处理结果
while (executeQuery.next()) {
//打印数据
//使用索引从1开始
//如果使用* 查询 索引是数据库表中的顺序;
//如果不使用 * 直接查询 使用字段
//那么索引顺序就是你sql 语句中的顺序
/*int sid = executeQuery.getInt(2);
String sname = executeQuery.getString(1);
double sprice = executeQuery.getDouble(3);
String sdesc = executeQuery.getString(4);
System.out.println(sid);
System.out.println(sname);
System.out.println(sprice);
System.out.println(sdesc);*/
//获取数据方法二 直接使用字段名
String string = executeQuery.getString("sname");
System.out.println(string);
}
//关闭资源
connection.close();
statement.close();
executeQuery.close();
}
键盘输入 账号 密码
查询数据库
并且打印该账号和密码
select * from users where usename = 'wanglong'
public static void main(String[] args) throws ClassNotFoundException, SQLException {
System.out.println("请输入账号");
Scanner scanner = new Scanner(System.in);
String usename = scanner.nextLine();
System.out.println("请输入密码");
String passwor = scanner.nextLine();
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "select * from users where username = '"+usename+"'and password ='"+passwor+"'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String string = resultSet.getString("username");
String string2 = resultSet.getString("password");
System.out.println(string);
System.out.println(string2);
}
connection.close();
statement.close();
resultSet.close();
}
wang'or'1=1
通过在sql 语句拼接时键入了一个恒等式 让这条语句一定成立
可以通过这个方式 来改变你SQL语句的原来含义
为了避免出现拼接时发生注入 于是在执行SQL语句时 我们先对SQL语句进行处理.
public static void main(String[] args) throws ClassNotFoundException, SQLException {
System.out.println("请输入账号");
Scanner scanner = new Scanner(System.in);
String usename = scanner.nextLine();
System.out.println("请输入密码");
String passwor = scanner.nextLine();
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mysql01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
//获取预编译SQL语句的对象
//使用占位符 ?
//注意 使用占位 时 不用加'' 单引号
String sql = "select * from users where "
+ "username = ?"
+ "and password = ?";
PreparedStatement statement = connection.prepareStatement(sql);
//设置占位符的值
//参数1 是? 的索引 从1开始
//参数2 替换问号的值
statement.setObject(1, usename);
statement.setObject(2, passwor);
//执行SQL语句
ResultSet executeQuery = statement.executeQuery();
//处理结果集
while (executeQuery.next()) {
System.out.println(executeQuery.getString("username")+" "+executeQuery.getString("password"));
}
connection.close();
statement.close();
executeQuery.close();
}
封装一个jdbc的工具类
package com.lanou3g;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/*
* 利用配置文件
* 封装连接数据库工具类
*/
public class JDBCUtilX {
public static String url;
public static String user;
public static String password;
public static String driver;
//静态注册
static {
try {
Properties properties = new Properties();
InputStream inputStream = JDBCUtilX.class.getClassLoader().getResourceAsStream("db.properties");
properties.load(inputStream);
driver = properties.getProperty("driverClass");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
try {
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("连接数据库失败");
}
}
//关闭资源
//把需要关闭的资源传进来
public static void close( Connection connection,PreparedStatement statement,ResultSet resultSet) {
//判断不为空再关
if (connection!=null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} if (resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
properties 文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql01
user=root
password=123456
查询goods表
并且将查出来的数据保存到对象中
并且放入集合中 遍历数据
public static void main(String[] args) throws SQLException {
ArrayList<Goods> list = new ArrayList<>();
Connection connection = JDBCUtilX.getConnection();
String sql = "select * from goods";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Goods g = new Goods(resultSet.getInt("sid"), resultSet.getString("sname")
, resultSet.getDouble("sprice"), resultSet.getString("sdesc"));
list.add(g);
}
JDBCUtilX.close(connection, statement, resultSet);
for (Goods goods : list) {
System.out.println(goods);
}
}