Java链接mySQL数据库进行增删改查_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;	}}