当前位置： 首页 > news >正文

网站的功能目标湖南正规seo优化

news 2025/8/17 17:57:58
网站的功能目标,湖南正规seo优化,设计网站 behance,什么是网站程序目录 一、获取数据库连接 1.1 方式一 1.2 方式二 1.3 方式三 1.4 方式四 1.5 方式五 二、增删改查操作 2.1 PreparedStatement介绍 2.2 添加数据 2.3 修改数据 2.4 删除数据 2.5 增删改的通用写法 2.6 查询customers表 2.7 查询customers表的通用写法…

目录

一、获取数据库连接

1.1  方式一

1.2  方式二

1.3  方式三

1.4  方式四

1.5  方式五

二、增删改查操作

2.1  PreparedStatement介绍

2.2  添加数据

2.3  修改数据

2.4  删除数据

2.5  增删改的通用写法

2.6  查询customers表

2.7  查询customers表的通用写法

2.8  查询order表的通用写法(推荐版)

2.9  针对不同表的通用查询方法

三、增删改查案例

3.1  向examstudent表中添加一行数据

3.2  根据身份证号或准考证号查询学生信息

3.3  根据准考证号删除学生信息

四、对Blob类型数据的操作

4.1  向customers表中插入Blob类型的字段

4.2  从customers表中读取Blob类型的字段

4.3  向表格中批量插入数据

五、事务的演示 

六、数据库连接池

6.1  C3P0数据库连接池

6.2  DBCP数据库连接池

6.3  DRUID数据库连接池

一、获取数据库连接

1.1  方式一

package cn.itcast_01;import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;import org.junit.Test;public class ConnectionTest {@Testpublic void testConnectin1() throws SQLException {// 创建驱动对象Driver driver = new com.mysql.jdbc.Driver();// 定位数据库String url = "jdbc:mysql://localhost:3306/test";// 将数据库的用户名和密码封装到Properties中Properties info = new Properties();info.setProperty("user", "root");info.setProperty("password", "root");// 返回驱动连接后的对象Connection conn = driver.connect(url, info);System.out.println(conn);}}

1.2  方式二

/*** 该方法不出现第三方api,程序具有更好的可移植性* @throws Exception*/@Testpublic void testConnection2() throws Exception  {//利用反射创建Driver对象Class clazz = Class.forName("com.mysql.jdbc.Driver");Driver driver = (Driver)clazz.newInstance();//提供连接的数据库String url = "jdbc:mysql://localhost:3306/test";//提供连接需要的用户名、密码Properties info = new Properties();info.setProperty("user", "root");info.setProperty("password", "root");// 返回驱动连接后的对象Connection conn = driver.connect(url, info);System.out.println(conn);}

1.3  方式三

/*** 该方法利用DriverManager提供方法来获取连接* @throws Exception*/@Testpublic void testConnection3() throws Exception {String url = "jdbc:mysql://localhost:3306/test";String user = "root";String password = "root";//获取Driver实现类对象Class clazz = Class.forName("com.mysql.jdbc.Driver");Driver driver = (Driver)clazz.newInstance();//注册驱动DriverManager.registerDriver(driver);//获取连接Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}

1.4  方式四

/*** 该方法将驱动的对象的创建和注册省略,* 实际上mysql默认可以创建和注册* "com.mysql.jdbc.Driver"这个字节码文件中有这样的操作* @throws Exception*/@Testpublic void testConnection4() throws Exception {//1.连接的基本信息String url = "jdbc:mysql://localhost:3306/test";String user = "root";String password = "root";//获取Driver实现类对象Class clazz = Class.forName("com.mysql.jdbc.Driver");//获取连接Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}

1.5  方式五

/*** 该方法将声明的4个基本信息声明在配置文件中,通过读取配置* 文件来获取连接* @throws Exception */@Testpublic void getConnection5() throws Exception {//1读取配置文件中的信息InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");Properties pros = new Properties();pros.load(is);String user = pros.getProperty("user");String password = pros.getProperty("password");String url = pros.getProperty("url");String driverClass = pros.getProperty("driverClass");//2.加载驱动Class.forName(driverClass);//3.获取连接Connection conn = DriverManager.getConnection(url,user,password);System.out.println(conn);}

二、增删改查操作

2.1  PreparedStatement介绍

  • 本身是接口，继承自Statement接口，Statement接口存在拼接字串和sql注入问题而被取代
  • 通过Connection类的对象调用如下方法，返回PreparedStatement的子类对象

        

  • 利用PreparedStatement的子类对象的方法对表进行操作

2.2  添加数据

/*** 使用PreparedStatement替换Statement实现对表的增删改查* @author Administrator*/
public class PreparedStatementUpdateTest {// 向customers表添加一条记录@Testpublic void testInsert() {// 定义连接对象和操作数据库对象Connection conn = null;PreparedStatement ps = null;try {// 1.获取连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，获取PreparedStatement的实例对象String sql = "insert into customers(name,email,birth)values(?,?,?)";// ?占位符ps = conn.prepareStatement(sql);// 3.填充占位符ps.setString(1, "哪吒");ps.setString(2, "nezha@gmail.com");SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-DD");java.util.Date date = sdf.parse("1000-01-01");ps.setDate(3, new Date(date.getTime()));// 4.执行操作ps.execute();} catch (Exception e) {e.printStackTrace();} finally {// 5.资源关闭JDBCUtils.closeResoure(conn, ps);}}
}

2.3  修改数据

        /*** 该方法用来测试修改操作* @throws Exception */@Testpublic void testUpdate() {//1.获取连接Connection conn = null;PreparedStatement ps = null;try {conn = JDBCUtils.getConnection();//2.预编译sql语句，返回PreparedStatement的实例String sql = "update customers set name = ? where id = ?";ps = conn.prepareStatement(sql);//3.填充占位符ps.setObject(1,"莫扎特");ps.setObject(2,18);//4.执行ps.execute();} catch (Exception e) {e.printStackTrace();} finally {//5.资源关闭JDBCUtils.closeResoure(conn, ps);}}

2.4  删除数据

/*** 该方法用来测试删除操作* @throws Exception */@Testpublic void testDelete() {Connection conn = null;PreparedStatement ps = null;try {//1.获取连接conn = JDBCUtils.getConnection();//2.预编译sql语句，返回PreparedStatement的实例String sql = "delete from customers where id = ?";ps = conn.prepareStatement(sql);//3.填充占位符ps.setObject(1,21);//4.执行ps.execute();} catch (Exception e) {e.printStackTrace();} finally {//5.资源关闭JDBCUtils.closeResoure(conn, ps);}}

2.5  增删改的通用写法

/*** 写一个通用的方法* @param sql sql语句* @param args 可变参数* @throws Exception */public void operation(String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;try {// 1.获取连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，返回PreparedStatement的实例ps = conn.prepareStatement(sql);// 3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);// 这里注意开始下标}// 4.执行ps.execute();} catch (Exception e) {e.printStackTrace();} finally {// 5.关闭资源JDBCUtils.closeResoure(conn, ps);}}

2.6  查询customers表

  •  ResultSet对象获得结果集
/*** 该方法用来测试查询customer表* * @throws Exception*/@Testpublic void testQuery1() {Connection conn = null;PreparedStatement ps = null;ResultSet resultSet = null;try {conn = JDBCUtils.getConnection();String sql = "select id,name,email,birth from customers where id = ?";ps = conn.prepareStatement(sql);ps.setObject(1, 1);// 执行并返回结果集resultSet = ps.executeQuery();/*** 判断结果集是否有数据,有数据返回true,指针下移 反之结束*/if (resultSet.next()) {// 获取当前数据的各个字段值int id = resultSet.getInt(1);String name = resultSet.getString(2);String email = resultSet.getString(3);Date birth = resultSet.getDate(4);Customer customer = new Customer(id, name, email, birth);System.out.println(customer);}} catch (Exception e) {e.printStackTrace();} finally {// 关闭资源JDBCUtils.closeResoure(conn, ps, resultSet);}}
  • 将查询得到的字段封装到类中
/*** 作用:该类用于封装'查询得到的结果集'字段* * 原理:ORM编程思想(object relational mapping)*  1.一个数据表对应一个java类*  2.表中的一条数据对应java的一个对象*  3.表中的一个字段对应java的一个属性*/
public class Customer {private int id;private String name;private String email;private Date birth;public Customer() {super();}public Customer(int id, String name, String email, Date birth) {super();this.id = id;this.name = name;this.email = email;this.birth = birth;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public Date getBirth() {return birth;}public void setBirth(Date birth) {this.birth = birth;}@Overridepublic String toString() {return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";}
}

2.7  查询customers表的通用写法

/*** 针对customers表的通用查询操作* @param sql * @throws Exception */public Customer queryForCustomers(String sql,Object...args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = JDBCUtils.getConnection();ps = conn.prepareStatement(sql);for(int i = 0; i < args.length; i++) {ps.setObject(i+1, args[i]);}rs = ps.executeQuery();//获取结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();//获取结果集的列数int columnCount = rsmd.getColumnCount();if(rs.next()) {Customer cust = new Customer();for(int i = 0; i < columnCount; i++) {//获取列值Object columnValue = rs.getObject(i+1);//获取列名String columnName = rsmd.getColumnName(i+1);//给cust对象指定的属性赋值为columnValueField field = Customer.class.getDeclaredField(columnName);field.setAccessible(true);field.set(cust, columnValue);}return cust;}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResoure(conn, ps,rs);}return null;}

2.8  查询order表的通用写法(推荐版)

/*** 该类是针对于order表的查询操作* @author Administrator**/
public class OrderForQuery {/*** 是查询order表的通用写法* @param sql* @param args* @return*/public Order queryForOrder(String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 1.创建连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，返回preparedStatement对象ps = conn.prepareStatement(sql);// 3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}// 4.获得结果集rs = ps.executeQuery();// 5.获得结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();// 6.获得结果集的列数int columnCount = rsmd.getColumnCount();// 7.将结果集中的每一行赋给一个Order对象if (rs.next()) {Order order = new Order();for (int i = 0; i < columnCount; i++) {// 获取列的别名String columnLabel = rsmd.getColumnLabel(i + 1);// 获取列值Object columnValue = rs.getObject(i + 1);// 给order对象指定的属性赋值为columnNameField field = Order.class.getDeclaredField(columnLabel);field.setAccessible(true);field.set(order, columnValue);}return order;}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResoure(conn, ps, rs);}return null;}/*** 该方法测试对order表的查询操作*/@Testpublic void testQueryForOrder() {String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";Order order = queryForOrder(sql, 4);System.out.println(order);}
}

2.9  针对不同表的通用查询方法

/*** 使用PreparedStatement实现针对于不同表的通用查询操作* @author Administrator**/
public class PreparedStatementQueryTest {@Testpublic void testGetInstance() {// 测试查询customers表中一条记录String sql = "select id,name,email from customers where id = ?";Customer customer = getInstance(Customer.class, sql, 12);System.out.println(customer);System.out.println("------------------------------------------");// 测试查询order表中一条记录String sql2 = "select order_id orderId,order_name orderName from `order` where order_id = ?";Order order = getInstance(Order.class, sql2, 1);System.out.println(order);System.out.println("------------------------------------------");// 测试查询customers表中多条记录String sql3 = "select id,name,email from customers where id < ?";List<Customer> list = getForList(Customer.class, sql3, 12);list.forEach(System.out::println);System.out.println("------------------------------------------");}/*** 该方法针对于不同表实现查询操作，返回表中一条记录* @param clazz 待传入的类的字节码文件对象* @param sql 待出入的sql语句* @param args 可变参数* @return 返回要查询的对象，也就是某一行 (<T>表示泛型参数类型)*/public <T> T getInstance(Class<T> clazz, String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 1.创建连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，返回preparedStatement对象ps = conn.prepareStatement(sql);// 3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}// 4.获得结果集rs = ps.executeQuery();// 5.获得结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();// 6.获得结果集的列数int columnCount = rsmd.getColumnCount();// 7.将结果集中的每一行赋给一个T对象if (rs.next()) {T t = clazz.newInstance();for (int i = 0; i < columnCount; i++) {// 获取列的别名String columnLabel = rsmd.getColumnLabel(i + 1);// 获取列值Object columnValue = rs.getObject(i + 1);// 给t对象指定的属性赋值为columnNameField field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columnValue);}return t;}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResoure(conn, ps, rs);}return null;}/*** 该方法针对于不同表实现查询操作，返回表中多条记录* @param clazz 待传入的类的字节码文件对象* @param sql 待出入的sql语句* @param args 可变参数* @return 返回多个对象*/public <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;List<T> list = new ArrayList<T>();try {// 1.创建连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，返回preparedStatement对象ps = conn.prepareStatement(sql);// 3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}// 4.获得结果集rs = ps.executeQuery();// 5.获得结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();// 6.获得结果集的列数int columnCount = rsmd.getColumnCount();// 7.将结果集中的每一行赋给一个T对象while (rs.next()) {T t = clazz.newInstance();for (int i = 0; i < columnCount; i++) {// 获取列的别名String columnLabel = rsmd.getColumnLabel(i + 1);// 获取列值Object columnValue = rs.getObject(i + 1);// 给t对象指定的属性赋值为columnNameField field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columnValue);}list.add(t);}return list;} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResoure(conn, ps, rs);}return null;}
}

三、增删改查案例

3.1  向examstudent表中添加一行数据

/*** 向examstudent表中添加一条记录* @author Administrator**/
public class Exer2Test {@Testpublic void testInsert() {Scanner scanner = new Scanner(System.in);System.out.print("四级/六级: ");int type = scanner.nextInt();System.out.print("身份证号: ");String IDCard = scanner.next();System.out.print("准考证号: ");String examCard = scanner.next();System.out.print("学生姓名: ");String studentName = scanner.next();System.out.print("所在城市: ");String location = scanner.next();System.out.print("考试成绩: ");int grade = scanner.nextInt();String sql = "insert into examstudent(type,IDCard,examCard,studentName,location,grade)" + "values(?,?,?,?,?,?)";int insertCount = operation(sql, type, IDCard, examCard, studentName, location, grade);if (insertCount > 0) {System.out.println("添加成功");} else {System.out.println("添加失败");}}/*** 该方法用来实现增删改* @param sql* @param args* @return*/public int operation(String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;try {// 1.获取连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，返回PreparedStatement的实例ps = conn.prepareStatement(sql);// 3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);// 这里注意开始下标}// 4.执行return ps.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {// 5.关闭资源JDBCUtils.closeResoure(conn, ps);}return 0;}
}

3.2  根据身份证号或准考证号查询学生信息

public class Exer2Test {/*** 该方法根据身份证号或准考证查询学生成绩*/@Testpublic void query() {System.out.println("请选择您要输入的类型: ");System.out.println("a.准考证号");System.out.println("b.身份证号");Scanner sc = new Scanner(System.in);String selection = sc.next();if ("a".equals(selection)) {System.out.println("请输入准考证号:");String examCard = sc.next();String sql = "select FlowID flowID,Type type,IDCard,"+ "ExamCard examCard,StudentName name,Location location,"+ "Grade grade from examstudent where examCard = ?";Student student = getInstance(Student.class, sql, examCard);if (student != null) {System.out.println(student);} else {System.out.println("输入的准考证号有误");}} else if ("b".equals(selection)) {System.out.println("请输入身份证号:");String IDCard = sc.next();String sql = "select FlowID flowID,Type type,IDCard,"+ "ExamCard examCard,StudentName name,Location location,"+ "Grade grade from examstudent where IDCard = ?";Student student = getInstance(Student.class, sql, IDCard);if (student != null) {System.out.println(student);} else {System.out.println("输入的身份证号有误");}} else {System.out.println("您的输入有误，请重新进入程序");}}/*** 该方法针对于不同表实现查询操作，返回表中一条记录* @param clazz 待传入的类的字节码文件对象* @param sql 待出入的sql语句* @param args 可变参数* @return 返回要查询的对象，也就是某一行 (<T>表示泛型参数类型)*/public <T> T getInstance(Class<T> clazz, String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {// 1.创建连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，返回preparedStatement对象ps = conn.prepareStatement(sql);// 3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}// 4.获得结果集rs = ps.executeQuery();// 5.获得结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();// 6.获得结果集的列数int columnCount = rsmd.getColumnCount();// 7.将结果集中的每一行赋给一个T对象if (rs.next()) {T t = clazz.newInstance();for (int i = 0; i < columnCount; i++) {// 获取列的别名String columnLabel = rsmd.getColumnLabel(i + 1);// 获取列值Object columnValue = rs.getObject(i + 1);// 给t对象指定的属性赋值为columnNameField field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columnValue);}return t;}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResoure(conn, ps, rs);}return null;}
}

3.3  根据准考证号删除学生信息

public class Exer2Test {/*** 该方法用来测试删除指定考号的学生*/@Testpublic void deleteTest() {System.out.println("请输入学生的考号: ");Scanner sc = new Scanner(System.in);String examCard = sc.next();String sql = "delete from examstudent where examCard = ?";int deleteCount = operation(sql, examCard);if (deleteCount > 0) {System.out.println("删除成功");} else {System.out.println("该学生不存在，请重新输入考号!");}}/*** 该方法用来实现增删改* @param sql* @param args* @return*/public int operation(String sql, Object... args) {Connection conn = null;PreparedStatement ps = null;try {// 1.获取连接conn = JDBCUtils.getConnection();// 2.预编译sql语句，返回PreparedStatement的实例ps = conn.prepareStatement(sql);// 3.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);// 这里注意开始下标}// 4.执行return ps.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {// 5.关闭资源JDBCUtils.closeResoure(conn, ps);}return 0;}
}

四、对Blob类型数据的操作

4.1  向customers表中插入Blob类型的字段

	/*** 该方法向customers表中插入Blob类型的字段* @throws Exception */@Testpublic void testInsert() {Connection conn = null;PreparedStatement ps = null;try {conn = JDBCUtils.getConnection();String sql = "insert into customers(name,email,birth,photo)" + "values(?,?,?,?)";ps = conn.prepareStatement(sql);ps.setObject(1, "佐助");ps.setObject(2, "Sasuke@qq.com");ps.setObject(3, "1993-09-08");FileInputStream is = new FileInputStream(new File("sasuke.jpg"));ps.setBlob(4, is);ps.execute();} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResoure(conn, ps);}}

4.2  从customers表中读取Blob类型的字段

	/*** 该方法查询customers表中Blob类型的字段* @throws Exception*/@Testpublic void testQuery() {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;InputStream is = null;FileOutputStream fos = null;try {conn = JDBCUtils.getConnection();String sql = "select id,name,email,birth,photo from customers where id = ?";ps = conn.prepareStatement(sql);ps.setInt(1, 22);rs = ps.executeQuery();if (rs.next()) {// 方式1/** int id = rs.getInt(1); String name = rs.getString(2); String* email = rs.getString(3); Date birth = rs.getDate(4); int id =* rs.getInt(1);*/// 方式2int id = rs.getInt("id");String name = rs.getString("name");String email = rs.getString("email");Date birth = rs.getDate("birth");Customer cust = new Customer(id, name, email, birth);System.out.println(cust);// 将Blob类型的字段下载下来，保存到本地Blob photo = rs.getBlob("photo");is = photo.getBinaryStream();fos = new FileOutputStream("Sasuke.jpg");byte[] buffer = new byte[1024];int len = 0;while ((len = is.read(buffer)) != -1) {fos.write(buffer, 0, len);}}} catch (Exception e) {e.printStackTrace();} finally {try {if (is != null) {is.close();}} catch (IOException e) {e.printStackTrace();}try {if (fos != null) {fos.close();}} catch (IOException e) {e.printStackTrace();}JDBCUtils.closeResoure(conn, ps, rs);}}

4.3  向表格中批量插入数据

/*** 实现批量插入* 1.addBatch()、executeBatch()、clearBatch()* 2.mysql服务器默认是关闭批处理的，需要通过一个参数开启mysql的批处理支持*   ?rewriteBatchedStatements=true 写在配置文件url的后面* 3.导入新的jar包5.1.37版本的* @throws Exception */@Testpublic void testInsert() {Connection conn = null;PreparedStatement ps = null;try {long start = System.currentTimeMillis();conn = JDBCUtils.getConnection();// 设置不允许自动提交conn.setAutoCommit(false);String sql = "insert into goods(name)values(?)";ps = conn.prepareStatement(sql);for (int i = 1; i <= 1000000; i++) {ps.setObject(1, "name_" + i);// 1."攒"sqlps.addBatch();if (i % 500 == 0) {// 2.执行batchps.executeBatch();// 3.情况batchps.clearBatch();}}// 提交数据conn.commit();long end = System.currentTimeMillis();System.out.println("花费的时间为: " + (end - start));} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.closeResoure(conn, ps);}}

五、事务的演示 

/*** 1.什么叫数据库事务?*   一组逻辑操作单元，使数据从一种状态变换为另一种状态。*   所谓逻辑操作单元，指的是一个或多个DML操作* * 2.事务的处理原则*   要么都执行，要么都不执行*  * 3.一旦提交，就不可回滚* * 4.哪些操作会导致数据的自动提交?*   1.DDL操作一旦执行，都会自动提交*   2.DML操作默认情况下，一旦执行，也会自动提交，但可控制*     通过set autocommit = false取消自动提交*   3.关闭连接时，未提交的数据会自动提交*   * 5.如何解决转账问题?*   1.修改update操作，不能执行一次就关闭连接一次，因为关闭连接*     会导致提交，这样就无法回滚到执行前*/
public class TransactionTest {/*** 演示AA用户给BB用户转账100*/@Testpublic void testUpdate() {Connection conn = null;try {conn = JDBCUtils.getConnection();// 下面这一句必须加，需要知道目前是否自动提交System.out.println(conn.getAutoCommit());// 设置取消数据的自动提交conn.setAutoCommit(false);String sql1 = "update user_table set balance = balance - 100 where user = ?";operation(conn, sql1, "AA");// 模拟网络异常// System.out.println(10 / 0);String sql2 = "update user_table set balance = balance + 100 where user = ?";operation(conn, sql2, "BB");System.out.println("转账成功");// 成功以后提交conn.commit();} catch (Exception e) {e.printStackTrace();// 回滚数据try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}} finally {// 针对数据库连接池时，需要恢复连接自动提交功能try {conn.setAutoCommit(true);} catch (SQLException e) {e.printStackTrace();}JDBCUtils.closeResoure(conn, null);}}// 通用的增删改操作public int operation(Connection conn, String sql, Object... args) {PreparedStatement ps = null;try {// 1.预编译sql语句，返回PreparedStatement的实例ps = conn.prepareStatement(sql);// 2.填充占位符for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);// 这里注意开始下标}// 3.执行/*** execute的返回值* 1.查询操作则return true* 2.增删改操作则return false;ps.execute();*/return ps.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {// 4.外面传进来的连接就不要关了JDBCUtils.closeResoure(null, ps);}return 0;}
}

六、数据库连接池

6.1  C3P0数据库连接池

  • xml文件的配置
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config><named-config name="helloc3p0"><!-- 提供获取连接的4个基本信息 --><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property><property name="user">root</property><property name="password">root</property><!-- 进行数据库连接池管理的基本信息 -->><!-- 当数据库连接池中连接数不够时，c3p0向数据库服务器申请的连接数 -->><property name="acquireIncrement">5</property><!-- c3p0数据库连接池中初始化时的连接数 -->><property name="initialPoolSize">10</property><!-- c3p0数据库连接池维护的最少连接数 -->><property name="minPoolSize">10</property><!-- c3p0数据库连接池维护的最多连接数 -->><property name="maxPoolSize">100</property><!-- c3p0最多维护的Statement的个数 -->><property name="maxStatements">50</property><!-- 每个连接中可以最多使用的Statement个数 -->><property name="maxStatementsPerConnection">2</property><!-- he's important, but there's only one of him --></named-config>
</c3p0-config>
  • c3p0测试
public class C3P0Test {@Testpublic void testGetConnection() throws Exception {ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0"); Connection conn = cpds.getConnection();System.out.println(conn);}
}
  • 封装到工具类
public class JDBCUtils {/*** c3p0获取数据库连接* @return* @throws SQLException*/private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");public static Connection getConnection1() throws SQLException {Connection conn = cpds.getConnection();return conn;}
}

6.2  DBCP数据库连接池

  • 配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root
  • dbcp测试
public class DBCPTest {/*** 测试DBCP数据库连接池技术* @throws Exception */@Testpublic void testGetConnection() throws Exception {// 创建DBCP的数据库连接池Properties pros = new Properties();InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");pros.load(is);DataSource source = BasicDataSourceFactory.createDataSource(pros);Connection conn = source.getConnection();System.out.println(conn);}
}
  • 封装到工具类
/*** 使用dbcp来获取数据库连接* @return* @throws Exception*/private static DataSource source;static {// 创建DBCP的数据库连接池try {Properties pros = new Properties();InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");pros.load(is);source = BasicDataSourceFactory.createDataSource(pros);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection2() throws Exception {Connection conn = source.getConnection();return conn;}

6.3  DRUID数据库连接池

  • 配置文件
url=jdbc:mysql://localhost:3306/test
username=root
password=root
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=10
  • druid测试
public class DruidTest {@Testpublic void getConnection() throws Exception {Properties pros = new Properties();InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");pros.load(is);DataSource source = DruidDataSourceFactory.createDataSource(pros);Connection conn = source.getConnection();System.out.println(conn);}
}
  • 封装到工具类
/*** 使用druid来获取数据库连接*/private static DataSource source1;static {try {Properties pros = new Properties();InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");pros.load(is);source1 = DruidDataSourceFactory.createDataSource(pros);} catch (Exception e) {e.printStackTrace();}}public static Connection getConnection3() throws SQLException {Connection conn = source1.getConnection();return conn;}

 

http://www.lbrq.cn/news/2736127.html

相关文章：

  • 菜单网站图片素材百度关键词推广怎么收费
  • 自己做的网站怎样对接支付宝windows优化软件
  • 昆山企业网站建设公司站内优化
  • 网站前台和后台对接实例惠州seo排名收费
  • 做网站推广需要花多少钱网络推广岗位职责和任职要求
  • 青岛网站建设价格站长之家爱站网
  • cvm服务器做网站营销型企业网站案例
  • 在北京做网站制作一个月多少钱微信朋友圈营销方案
  • 网站建设开发费用爱站网综合查询
  • 简单网页制作模板下载seo关键词搜索和优化
  • 工作室推广网站推送者seo
  • 品牌网站建设信息提高基层治理效能
  • 全球疫情最新消息数据seo快速排名软件
  • 阿里云网站建设9元免费留电话的广告
  • 拼多多网站开发百度手机助手网页
  • 成都网站设计推荐天津seo排名公司
  • 网站制作公司的流程磁力宅
  • 人员优化方案电子商务seo是什么意思
  • wordpress视频防止下载文件惠州seo推广外包
  • 网站制作用的软件有哪些今日热搜第一名
  • 做网站需要掌握什么建站之星
  • 武汉手机网站建设公司竞价网官网
  • 做购物网站是怎么连接银行什么是网络推广
  • 如何注册海外域名优化是什么意思?
  • 国外做彩票网站违法吗手机端网站排名
  • 南宁百度网站建设公司百度关键词搜索推广
  • 用asp.net做网站的书百度贴吧网页版入口
  • 中企动力做的网站被百度屏蔽小程序开发公司排行榜
  • php 网站做分享功能抖音搜索引擎推广
  • 达人室内设计网站百度竞价开户费用
  • Fixture Caliper 工具
  • Jenkins安装部署(Win11)和常见配置镜像加速
  • leetcodehot100 矩阵置零
  • C++STL标准模板库详解
  • Git Revert 特定文件/路径的方法
  • uniapp纯前端绘制商品分享图