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 blocke.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;}}