java web jdbc操作总结

2019独角兽企业重金招聘Python工程师标准>>>

1.直接上代码

package com.aslan.jdbczongjie;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Demo1 {
	
	/*使用最原始的方式来建立连接*/
	@Test
	public void fun1()
	{
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		
		try {
			
			//1.加载mysql驱动
			Class.forName("com.mysql.jdbc.Driver");
			
			//2.生成Connection对象		
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
			System.out.println(conn);
			
			//3.建立sql模版
			String sql = "insert into stu values(?,?)";
			
			//4.创建PreparedStatement
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, "1xiaoliba1");
			preparedStatement.setDouble(2, 101);
			
			//5.执行sql语句
			int result =  preparedStatement.executeUpdate();
			
			System.out.println(result);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally
		{
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
			if(preparedStatement != null)
			{
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	/*
	 * 使用配置文件来配置参数
	 * */
	@Test
	public void fun2() throws Exception
	{
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		
		//1.创建props对象，加载参数
		Properties props = new Properties();
		InputStream in = this.getClass().getClassLoader().getResourceAsStream("dbconfig.properties");		
		props.load(in);
		
		//2.加载驱动
		Class.forName(props.getProperty("driverName"));
		
		//3.获取Connection
		conn = DriverManager.getConnection(props.getProperty("jdbcUrl"),props.getProperty("user"),
											props.getProperty("password"));
		
		//4.创建PreparedStatement
		String sql = "insert into stu values(?,?)";
		preparedStatement = conn.prepareStatement(sql);
		preparedStatement.setString(1, "jiajia");
		preparedStatement.setDouble(2, 11.1);
		
		//5.执行sql语句
		int result = preparedStatement.executeUpdate();
		
		System.out.println(result);
		
	
	}
	
	/*
	 * 封装Jdbcutils来简化操作
	 * */
	@Test
	public void fun3() throws SQLException
	{
		PreparedStatement preparedStatement = null;
		Connection conn = Jdbcutils.getConnection();

		//4.创建PreparedStatement
		String sql = "insert into stu values(?,?)";
		preparedStatement = conn.prepareStatement(sql);
		preparedStatement.setString(1, "jiajia1");
		preparedStatement.setDouble(2, 11.1);
		
		//5.执行sql语句
		int result = preparedStatement.executeUpdate();
		
		System.out.println(result);
	}
	
	/*
	 * jdcb 使用事务
	 * */
	
	@Test
	public void fun4()
	{
		PreparedStatement preparedStatement = null;
		Connection conn = null;
		
		try {
			
			//1.获取连接
			conn = Jdbcutils.getConnection();
			
			//2.关闭自动commit
			conn.setAutoCommit(false);
		
			
			//3..创建PreparedStatement
			String sql = "update stu set money=? where username=? ";
			preparedStatement = conn.prepareStatement(sql);
			
			//4.插入一条语句
			preparedStatement.setDouble(1, 100);
			preparedStatement.setString(2, "aslan");
			
			//5.执行sql语句
			int result = preparedStatement.executeUpdate();
			
			if(true)
				throw new RuntimeException("测试一下");
			
			//4.插入一条语句
			preparedStatement.setDouble(1, 101);
			preparedStatement.setString(2, "kaka");
			
			//5.执行sql语句
			result = preparedStatement.executeUpdate();
			
			
			conn.commit();
				
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	/*
	 * 使用数据库连接池
	 * 1.导入包: commons-dbcp-1.4.jar commons-pool.1.6.jar
	 * 2.
	 * */
	@Test
	public void  fun5()   {

		try {
			//1.创建数据库连接池
			BasicDataSource dataSource = new BasicDataSource();
			
			//2.初始化池参数
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://localhost:3306/test");
			dataSource.setUsername("root");
			dataSource.setPassword("root");
			
			//3.获取Connection连接
			Connection conn = dataSource.getConnection();

			//4.关闭conn ，本质是把连接归还到连接池
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/*
	 * 使用c3p0 数据库连接池
	 * 需要导入下面三个jar包
	 * 		c3p0-0.9.5.2.jar
	 *		c3p0-oracle-thin-extras-0.9.5.2.jar
	 *		mchange-commons-java-0.2.11.jar
	 * */
	@Test
	public void fun6() throws Exception
	{
		//1.创建连接池对象
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		
		//2.加载连接池参数
		dataSource.setDriverClass("com.mysql.jdbc.Driver");
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
		dataSource.setUser("root");
		dataSource.setPassword("root");
		
		//3.获取Connection连接
		Connection conn = dataSource.getConnection();
		
		System.out.println(conn);
		
		//4.关闭conn ，本质是把连接归还到连接池
		conn.close();
	}
	
	/*
	 * 使用c3p0数据库连接池，使用配置文件来初始化参数
	 * 1.需要3个必备的jar包
	 * 2.需要 src目录下的 cp30-config.xml配置文件
	 * */
	
	@Test
	public void fun7() throws SQLException
	{
		//1.创建连接池对象
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		
		//2.获取Connection对象
		Connection conn = dataSource.getConnection();
		
		//3.
		System.out.println(conn);

		//4.关闭conn
		conn.close();
	}
	
	/*
	 * 使用c3p0数据库连接池，使用配置文件来初始化参数
	 * 1.需要3个必备的jar包
	 * 2.需要 src目录下的 cp30-config.xml配置文件
	 * */
	
	@Test
	public void fun8() throws SQLException
	{
		//1.创建连接池对象
		ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql-config");
		
		//2.获取Connection对象
		Connection conn = dataSource.getConnection();
		
		//3.
		System.out.println(conn);

		//4.关闭conn
		conn.close();
	}
	
	/*
	 * dbutils的使用
	 * 依赖的jar包 commons-dbutils-1.6.jar
	 *  另外需要的文件：c3p0-config.xml 和 jdbcutils.java
	 * */	
	@Test
	public  void  fun9() throws SQLException {
		
		//1.创建qr对象
		QueryRunner qr = new QueryRunner(Jdbcutils.getDataSource());
		
		//2.给出sql模版
		String sql = "insert into stu values(?,?)";
		
		//3.给出sql参数
		Object[] params = {"xiaojiaa",1000};
		
		//4.执行sql语句
		qr.update(sql,params);
		
	}
	
}

2.当前学到的点就是上面列出来的

转载于:https://my.oschina.net/aslanjia/blog/846591