JDBC实现基本的CRUD示例
private static void insertTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.execute("insert into user(ID,NAME) values(1, 'Zhang San')");
st.execute("insert into user(ID,NAME) values(2, 'Li Si')");
st.execute("insert into user(ID,NAME) values(3, 'Wang Wu')");
System.out.println("=====insert test=====");
showUser(st);
st.close();
con.close();
}
private static void deleteTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.execute("delete from user where ID=3");
System.out.println("=====delete test=====");
showUser(st);
st.close();
con.close();
}
private static void updateTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.executeUpdate("update user set NAME='TEST' where ID=2");
System.out.println("=====update test=====");
showUser(st);
st.close();
con.close();
}
private static void showUser(Statement st) throws SQLException
{
ResultSet rs = st.executeQuery("select ID, NAME from user");
while(rs.next())
{
int id = rs.getInt("ID");
String name = rs.getString("NAME");
System.out.println("ID:" + id + "; NAME=" + name);
}
rs.close();
private static void insertTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.execute("insert into user(ID,NAME) values(1, 'Zhang San')");
st.execute("insert into user(ID,NAME) values(2, 'Li Si')");
st.execute("insert into user(ID,NAME) values(3, 'Wang Wu')");
System.out.println("=====insert test=====");
showUser(st);
st.close();
con.close();
}
private static void deleteTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.execute("delete from user where ID=3");
System.out.println("=====delete test=====");
showUser(st);
st.close();
con.close();
}
private static void updateTest() throws SQLException
{
String dbURL = "jdbc:mysql://localhost/test";
Connection con = DriverManager.getConnection(dbURL, "root", "123");
Statement st = con.createStatement();
st.executeUpdate("update user set NAME='TEST' where ID=2");
System.out.println("=====update test=====");
showUser(st);
st.close();
con.close();
}
private static void showUser(Statement st) throws SQLException
{
ResultSet rs = st.executeQuery("select ID, NAME from user");
while(rs.next())
{
int id = rs.getInt("ID");
String name = rs.getString("NAME");
System.out.println("ID:" + id + "; NAME=" + name);
}
rs.close();
}
1、数据库连接 使用时就创建 不使用立即释放 对数据库进行频繁的开启和关闭 造成数据库资源的浪费
影响数据库的性能
设想:使用数据库连接池管理数据库连接
2、将sql语句硬编码到Java代码中,如果将sql语句进行修改 需要重新编译java代码 不利于系统维护
设想:将sql语句配置在xml的配置文件中 即使sql变化也不需要重新编译
3、向prepareStatement中设置参数 ,对占位符号的位置和设置的参数值,硬编码在java代码中,不利于系统维护
sql语句及占位符和参数全部配置在xml中
4、从resultSet中遍历结果数据时 存在硬编码问题 将要获取表的字段进行硬编码 不利于维护
设想将查询结果 自动映射成java对象