Statement普通语句执行平台
package com.offcn.jabc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import com.offcn.entity.User;
public class JABCDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
selectDemo();
}
//添加数据
public static void insertDemo() throws ClassNotFoundException, SQLException {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//准备三要素
String url = "jdbc:mysql://localhost:3306/day3?useSSL=true";
String user = "root";
String password = "root";
//获取数据库连接对象
Connection connection = DriverManager.getConnection(url, user, password);
//获取语句执行平台
Statement statement = connection.createStatement();
//执行sql语句
int row = statement.executeUpdate("insert into user values (null,'赵奕欢','111','女')");
//展示结果
System.out.println(row > 0 ? "添加成功" : "添加失败");
//释放资源
statement.close(); //语句平台
connection.close(); //连接对象
}
//修改数据
public static void updateDemo() {
/*因为资源的关闭需要在finally中书写，需要将语句平台以及连接对象提升作用域
放在try catch finally 外面声明！
在try内部赋值！ */
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
//给在外界声明的连接对象以及语句平台对象赋值！
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day3",
"root","root");
statement = connection.createStatement();
int row = statement.executeUpdate("update user set uname = '吴京' where uid = 22");
System.out.println(row > 0 ? "修改成功" : "修改失败");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
/*关闭资源时，按照后创建的优先关闭的原则书写关闭代码
关闭资源时要防止空指针异常的产生，所以添加判断条件*/
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//删除数据
public static void deleteDemo() {
/*因为资源的关闭需要在finally中书写，需要将语句平台以及连接对象提升作用域
放在try catch finally 外面声明！
在try内部赋值！ */
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
//给在外界声明的连接对象以及语句平台对象赋值！
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day3",
"root","root");
statement = connection.createStatement();
int row = statement.executeUpdate("delete from user where uname = '周冬雨2'");
System.out.println(row > 0 ? "删除成功" : "删除失败");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
/*关闭资源时，按照后创建的优先关闭的原则书写关闭代码
关闭资源时要防止空指针异常的产生，所以添加判断条件*/
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//查询数据
public static void selectDemo() {
Connection connection = null;
Statement statement = null;
ResultSet set = null;
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//三要素准备
String url = "jdbc:mysql://localhost:3306/day3?useSSL=true";
String user = "root";
String password = "root";
//获取数据库连接对象
connection = DriverManager.getConnection(url,user,password);
//获取执行语句平台
statement = connection.createStatement();
//执行sql语句
/*
语句平台执行查询sql语句时，调用的方法为executeQuery(查询的sql语句)
方法的返回值为ResultSet结果集，查询语句得到的结果全部封装到结果集中
*/
String sql = "select * from user";
set = statement.executeQuery(sql);
//创建List集合储存ResultSet set中的内容
List<User> users = new ArrayList<>();
//遍历ResultSet set
while(set.next()) {
int uid = set.getInt("uid");
String uname = set.getString("uname");
String pwd = set.getString("pwd");
String gender = set.getString("gender");
//创建User类的对象
User user1 = new User(uid, uname, pwd, gender);
users.add(user1);
}
for (User user2 : users) {
System.out.println(user2);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
//查询语句时，结果集也要释放~
if (set != null) {
set.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
PreparedStatement预编译语句平台
package com.offcn.homework;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class Login {
public static void main(String[] args) {
safeRegister() ;
}
//注册案例
public static void safeRegister() {
Scanner sc = new Scanner(System.in);
System.out.println("请输入要注册的用户名");
String uname = sc.nextLine();
System.out.println("请输入要注册的密码");
String pwd = sc.nextLine();
System.out.println("请输入你的性别");
String gender = sc.nextLine();
//判断是否注册成功就是判断数据库中书否存在
//也就是查询 只需要查询用户名水是否存在即可
Connection connection = JDBCUtils.getConnection();
PreparedStatement pStat = null;
ResultSet set = null;
String sql = "Select * from user where uname = ?";
try {
pStat = connection.prepareStatement(sql);
pStat.setObject(1, uname);
set = pStat.executeQuery();
System.out.println(set.next()? "注册失败" : "注册成功");
if (set.next()? true : false ) {
System.out.println("您输入的用户名已被占用");
}else {
pStat = connection.prepareStatement("insert into user values(null,?,?,?)");
pStat.setObject(1, uname);
pStat.setObject(2, pwd);
pStat.setObject(3, gender);
int row = pStat.executeUpdate();
System.out.println(row>0?"数据写入成功":"数据写入失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeMethod(connection, pStat, set);
}
}
//安全登录案例
public static void safeLogin() {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String uname = sc.nextLine();
System.out.println("请输入密码");
String pwd = sc.nextLine();
Connection connection = JDBCUtils.getConnection();
PreparedStatement pStat = null;
ResultSet set = null;
String sql = "Select * from user where uname = ? and pwd = ?";
try {
pStat = connection.prepareStatement(sql);
pStat.setObject(1, uname);
pStat.setObject(2, pwd);
set = pStat.executeQuery();
System.out.println(set.next()? "登录成功" : "登录失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeMethod(connection, pStat, set);
}
}
}
JDBC工具类的封装
package com.offcn.homework;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author Administrator
* 此类专门用于封装jdbc操作
* 封装获取数据库连接Connection的方法
* 封装获取语句平台Statement的方法
* 封装释放资源的方法
*
* 工具类封装有什么要求呢？
* 1、工具类中的方法为了方便调用，都是类方法 static
* 2、方法内部如果出现了异常，只能用try catch捕获，不能throws抛出
* 3、工具类方法在设计时，需要遵循高内聚、低耦合，方法与方法之间互相独立、互不干扰。
*/
public class JDBCUtils {
//声明连接对象
private static Connection connection;
//获取连接对象Connection的方法
public static Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/day3";
String username = "root";
String password = "root";
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
//封装获取普通语句平台的方法
public static Statement getStat() {
try {
return connection.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//封装获取预编译语句平台的方法
public static PreparedStatement getPstat(String sql, Object[] params) {
try {
PreparedStatement pStat = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
pStat.setObject(i + 1, params[i]);
}
}
return pStat;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//封装释放资源的方法，增删改
public static void closeMethod(Connection connection, Statement statement) {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//封装释放资源的方法，查询
public static void closeMethod(Connection connection, Statement statement, ResultSet set) {
try {
if (set != null) {
set.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
数据库连接池:
dbcp操作代码:
package com.offcn.dbcp;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.offcn.entity.User;
public class DBCPDemo {
public static void main(String[] args) {
/*
使用dbcp数据源来操作数据库
方式1：在创建QueryRunner时，给定数据源，此时不需要书写获取连接的代码
会自动从数据源中获取数据库连接对象，而且不需要书写关闭资源的方法，因为连接对象使用完成之后
会自动归还数据库连接池！！！
方式2：我们可以通过数据源对象.getConnection()获取到由数据库连接池管理的数据库连接对象
使用完毕后，关闭资源，并不是将连接对象销毁，而是将连接对象归还池子
*/
//方式1
/*
QueryRunner qRunner = new QueryRunner(DBCPUtils.getDataSource());
//此时连接自动获取，执行sql语句时，不需要再传入连接对象connection了
try {
List<User> users = qRunner.query("select * from user", new BeanListHandler<User>(User.class));
for (User user : users) {
System.out.println(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
/*
QueryRunner qRunner = new QueryRunner(DBCPUtils.getDataSource());
try {
int row =
qRunner.update("insert into user values (null, ?, ?, ?)", "渣男程", "438", "妖怪");
System.out.println(row > 0 ? "添加成功" : "添加失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
//方式2
Connection connection = null;
try {
//从数据源中获取到连接对象
connection = DBCPUtils.getDataSource().getConnection();
//创建QueryRunner
QueryRunner qRunner = new QueryRunner();
int row = qRunner.update(connection, "update user set uname = ? where uid = ?", "小菜花", 8);
System.out.println(row > 0 ? "修改成功" : "修改失败");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
//此时关闭连接对象，是归还池子，不是销毁！
DbUtils.close(connection);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
dbcp数据源封装类代码:
package com.offcn.dbcp;
import java.io.FileInputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
/**
* 此类只有一个目的，为QueryRunner提供创建时的数据源！！！
*/
public class DBCPUtils {
//数据源对象
private static DataSource dataSource = null;
/*
数据源、数据库连接池对象，在应用程序只需要一个就够了，池子中的连接对象是多个的！
所以要保证获取到的数据源只有，那么赋值过程只能走一次，我们可以放在静态代码块中执行
*/
static {
//dbcp创建数据源时需要的属性文件对象
Properties properties = new Properties();
FileInputStream fStream = null;
try {
/*
加载dbcp.properties配置文件中配置给连接池、数据源的信息
properties对象的load()方法需要传入一个输入流对象，来将输入流读取的内容加载到属性文件对象中
所以此时我们需要创建一个输入流对象来读取dbcp.properties，然后传入到load()方法中作为参数
*/
fStream = new FileInputStream("src/dbcp.properties");
properties.load(fStream);
//将加载完配置信息的属性文件对象，往dbcp生成数据源的方法中一传，那么数据源就有了配置信息
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取数据源
public static DataSource getDataSource() {
//返回dbcp中的数据源
return dataSource;
}
}
Druid操作代码
package com.offcn.druid;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import com.offcn.entity.User;
public class DruidDemo {
public static void main(String[] args) {
QueryRunner qRunner = new QueryRunner(DruidUtils.getDataSource());
try {
User user =
qRunner.query("select * from user where uid = ?", new BeanHandler<User>(User.class), 14);
qRunner.update("delete from user where uname = '赵云'");
System.out.println(user);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Druid数据源封装类代码:
package com.offcn.druid;
import java.io.FileInputStream;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
/**
*
* @author Administrator
* 此类专门为QueryRunner提供数据源
*/
public class DruidUtils {
private static DataSource dataSource = null;
static {
Properties properties = new Properties();
FileInputStream fStream = null;
try {
fStream = new FileInputStream("src/druid.properties");
properties.load(fStream);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static DataSource getDataSource() {
return dataSource;
}
}
c3p0操作代码
package com.offcn.c3p0;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.offcn.entity.User;
public class C3P0Demo {
public static void main(String[] args) {
//ComboPooledDataSource 就是c3p0的数据源，而且数据源的配置信息已经自动读取过了~
QueryRunner qRunner = new QueryRunner(new ComboPooledDataSource());
try {
List<User> users = qRunner.query("select * from user", new BeanListHandler<User>(User.class));
for (User user : users) {
System.out.println(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
1759
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
