Java链接mySQL数据库进行增删改查

Java链接mySQL数据库代码

改和查对于增加是一样的

public class JDBCTest {
	/**
	 * ResultSet封装了JDBC结果集，进行查询的结果
	 */
	@Test
	public void testResultSet() {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			connection = JDBCTools.getConnection();
			statement = (Statement) connection.createStatement();
			String sql = "SELECT * FROM jdbc_1";
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				System.out.print(rs.getInt(1) + "\t");
				System.out.print(rs.getString(2) + "\t");
				System.out.print(rs.getString(3));
				System.out.println();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCTools.release(rs, statement, connection);
		}
	}
	/**
	 * 通用更新方法
	 * @throws SQLException 
	 */
	public void updata(String sql) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			// 获取Statement 对象
			statement = (Statement) connection.createStatement();
			// 执行sql语句
			statement.executeUpdate(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				// 关闭statement对象
				if (statement != null) {
					statement.close();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				// 关闭数据库连接
				if (connection != null) {
					connection.close();
				}
			}
		}
	}
	
	/**
	 * 1.通过jdbc向指定表中插入数据
	 * 
	 * @throws Exception
	 * 
	 */
	@Test
	public void testStatement() throws Exception {
		// 获取数据库链接
		Connection connection = null;
		Statement statement = null;
		try {
			connection = getConnection();
			// 要执行的sql语句
			String sql = "INSERT INTO jdbc_1 (jname,addr) VALUE ('李力','浑江市')";
			// 获取Statement 对象
			statement = (Statement) connection.createStatement();
			// 执行sql语句
			statement.executeUpdate(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				// 关闭statement对象
				if (statement != null) {
					statement.close();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				// 关闭数据库连接
				if (connection != null) {
					connection.close();
				}
			}
		}
	}

	/**
	 * DriverManager类是驱动的管理类 利用DriverManager连接数据库，进行数据库驱动注册
	 * 
	 * @throws Exception
	 */
	@Test
	public void testDriverManager() throws Exception {
		String driverClass = null;
		String jdbcUrl = null;
		String user = null;
		String password = null;
		// 读取properties文件
		InputStream in = getClass().getClassLoader().getResourceAsStream(
				"jdbc.properties");
		Properties properties = new Properties();
		properties.load(in);
		driverClass = properties.getProperty("driver");
		jdbcUrl = properties.getProperty("url");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
		Class.forName(driverClass);
		Connection connection = (Connection) DriverManager.getConnection(
				jdbcUrl, user, password);
		System.out.println(connection);
	}

	/**
	 * 驱动测试，对链接驱动进行测试
	 * 
	 * @throws SQLException
	 * 
	 */
	@Test
	public void testDriver() throws SQLException {
		// 创建Drivers实现类
		Driver driver = new com.mysql.jdbc.Driver();
		String url = "jdbc:mysql://localhost:3306/jdbc";
		Properties info = new Properties();
		info.put("user", "root");
		info.put("password", "root");
		// 调用driver接口的Connection
		Connection connection = (Connection) driver.connect(url, info);
		System.out.println(connection);

	}

	/**
	 * 编写通用方法获取任意数据库链接，不用修改源程序
	 * 
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws SQLException
	 * @throws IOException
	 */
	public Connection getConnection() throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException,
			IOException {
		String driverClass = null;
		String jdbcUrl = null;
		String user = null;
		String password = null;
		// 读取properties文件
		InputStream in = getClass().getClassLoader().getResourceAsStream(
				"jdbc.properties");
		Properties properties = new Properties();
		properties.load(in);
		driverClass = properties.getProperty("driver");
		jdbcUrl = properties.getProperty("url");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
		Driver driver = (Driver) Class.forName(driverClass).newInstance();
		Properties info = new Properties();
		info.put("user", user);
		info.put("password", password);
		Connection connection = (Connection) driver.connect(jdbcUrl, info);
		return connection;
	}

	@Test
	public void testConnection() throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException,
			IOException {
		System.out.println(getConnection());
	}

}

public class JDBCTools {
	/**
	 * 结果查询关闭
	 * @param rs
	 * @param statement
	 * @param conn
	 */
	public static void release(ResultSet rs,Statement statement, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	/**
	 * 数据库更新方法
	 * @param sql
	 */
	public void uodate(String sql) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = JDBCTools.getConnection();
			statement = (Statement) connection.createStatement();
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCTools.release(statement, connection);
		}
	}

	/**
	 * 关闭数据库连接的方法
	 * @param statement
	 * @param conn
	 */
	public static void release(Statement statement, Connection conn) {
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}

	/**
	 * 编写通用方法获取任意数据库链接，不用修改源程序
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws SQLException
	 * @throws IOException
	 */
	public static Connection getConnection() throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException,
			IOException {
		String driverClass = null;
		String jdbcUrl = null;
		String user = null;
		String password = null;
		// 读取properties文件
		InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream(
				"jdbc.properties");
		Properties properties = new Properties();
		properties.load(in);
		driverClass = properties.getProperty("driver");
		jdbcUrl = properties.getProperty("url");
		user = properties.getProperty("user");
		password = properties.getProperty("password");
		Driver driver = (Driver) Class.forName(driverClass).newInstance();
		Properties info = new Properties();
		info.put("user", user);
		info.put("password", password);
		Connection connection = (Connection) driver.connect(jdbcUrl, info);
		return connection;
	}
}