一、修改数据
方法一
代码实现:
public static void opDBByNormal() {
DruidDataSource dds = new DruidDataSource();
dds.setUsername("root");
dds.setPassword("root");
dds.setUrl("jdbc:mysql://localhost:3306/system");
dds.setDriverClassName("com.mysql.jdbc.Driver");
dds.setInitialSize(5);
dds.setMaxActive(20);
Connection conn = null;
Statement stat = null;
try {
conn = dds.getConnection();
stat = conn.createStatement();
int affected = stat.executeUpdate("update user_infer set age = 20 where id = 3");
if(affected > 0) {
System.out.println("数据库操作成功!!!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {}
try {
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
方法二:
代码实现:
public static void opDBbyCommonsDB() {
QueryRunner qr = new QueryRunner(DBUtil.getDS());
String sql = "update user_info set age = ? where id = ?";
try {
if(qr.update(sql, 30, 3) > 0) {
System.out.println("数据修改成功!!!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
读取数据
思路:
将数据库里的数据读取到一个对象中储存起来,然后打印对象
方法一:
代码实现:
public static void queryDatabyCommonsDB2() {
QueryRunner qr = new QueryRunner(DBUtil.getDS());
try {
String sql = "select * from user_info where id = ?";
UserInfo ui = qr.query(sql, new ResultSetHandler<UserInfo>() {
@Override
public UserInfo handle(ResultSet rs) throws SQLException {
UserInfo ui = new UserInfo();
if(rs.next()) {
ui.setUserName(rs.getString("user_name"));
ui.setPasswd(rs.getString("password"));
ui.setRealName(rs.getString("real_name"));
ui.setAge(rs.getInt("age"));
ui.setId(rs.getInt("id"));
}
return ui;
}
}, 1);
System.out.println("用户信息:" + ui);
} catch (SQLException e) {
e.printStackTrace();
}
}
方法二:
问题:
数据库里的信息命名规则与java中的不相同
解决方法:
将数据库里的信息进行重命名
代码实现:
public static void queryDatabyCommonsDB() {
QueryRunner qr = new QueryRunner(DBUtil.getDS());
try {
String sql = "select id, user_name userName, password passwd, "
+ "real_name realName, age from user_info where id = ?";
UserInfo ui = qr.query(sql, new BeanHandler<UserInfo>(UserInfo.class), 1);
System.out.println("用户信息s:" + ui);
} catch (SQLException e) {
e.printStackTrace();
}
}
将数据库里的信息全部读取出来:
代码实现:
public static void queryDatabyCommonsDB3() {
QueryRunner qr = new QueryRunner(DBUtil.getDS());
try {
String sql = "select id, user_name userName, password passwd, "
+ "real_name realName, age from user_info";
List<UserInfo> uiList = qr.query(sql, new BeanListHandler<UserInfo>(UserInfo.class));
System.out.println("用户信息s:" + uiList);
} catch (SQLException e) {
e.printStackTrace();
}
}