public class TestDao {
/**
* 添加指定数据表的数据
*/
public void insertUser() {
try {
//导入配置文件
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//获取参数
String driver = properties.getProperty("jdbc.driver");
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
//
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setJdbcUrl(driver);
driverManagerDataSource.setJdbcUrl(url);
driverManagerDataSource.setUser(username);
driverManagerDataSource.setPassword(password);
//数据库语句
String sql = "insert into user (username,password,email,root,register_time) values (?,?,?,?,?) ";
//
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
int rows = jdbcTemplate.update(sql,"rabbit","rabbit","rabbit@qq.com",1,"2019-3-2");
System.out.println("rows="+rows);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 更新指定数据表的数据
*/
public void updateUser() {
try {
//导入配置文件
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//获取参数
String driver = properties.getProperty("jdbc.driver");
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
//
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setJdbcUrl(driver);
driverManagerDataSource.setJdbcUrl(url);
driverManagerDataSource.setUser(username);
driverManagerDataSource.setPassword(password);
//数据库语句
String sql = "update user set phone = ? where username = ?";
//
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
int rows = jdbcTemplate.update(sql,"13812392132","rabbit");
System.out.println("rows=" + rows);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 查询全部数据
*/
public void selectAllUser() {
try {
//导入配置文件
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//获取参数
String driver = properties.getProperty("jdbc.driver");
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
//
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setJdbcUrl(driver);
driverManagerDataSource.setJdbcUrl(url);
driverManagerDataSource.setUser(username);
driverManagerDataSource.setPassword(password);
//数据库语句
String sql = "select * from user";
//
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
System.out.println("maps=" + maps);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 删除指定数据
*/
public void deleteUser() {
try {
//导入配置文件
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//获取参数
String driver = properties.getProperty("jdbc.driver");
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
//
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setJdbcUrl(driver);
driverManagerDataSource.setJdbcUrl(url);
driverManagerDataSource.setUser(username);
driverManagerDataSource.setPassword(password);
//数据库语句
String sql = "delete from user where username = ?";
//
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
int row = jdbcTemplate.update(sql, "rabbit");
System.out.println("row=" + row);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 查询指定数据表的记录数
*/
public void selectCountUser() {
try {
//导入配置文件
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//获取参数
String driver = properties.getProperty("jdbc.driver");
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
//
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setJdbcUrl(driver);
driverManagerDataSource.setJdbcUrl(url);
driverManagerDataSource.setUser(username);
driverManagerDataSource.setPassword(password);
//数据库语句
String sql = "select count(*) from user";
//
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
//第一个参数sql语句，第二个参数返回的数据类型
Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
System.out.println("count=" + count);
} catch (IOException e) {
e.printStackTrace();
}
}
public void selectUser();() {
try {
//导入配置文件
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//获取参数
String driver = properties.getProperty("jdbc.driver");
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
//
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setJdbcUrl(driver);
driverManagerDataSource.setJdbcUrl(url);
driverManagerDataSource.setUser(username);
driverManagerDataSource.setPassword(password);
//数据库语句
String sql = "select * from user where username = ?";
User user = jdbcTemplate.queryForObject(sql, new NewRowMapper(), "rabbit");
JSONObject jsonObject = JSONObject.fromObject(user);
System.out.println(jsonObject + "");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 处理查询结果集
*/
class NewRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
try {
Class<?> newClass = Class.forName("cn.muriel.auto.pojo.User");
Object o = newClass.newInstance();
for (int j = 1; j <= resultSet.getMetaData().getColumnCount(); j++) {
String name = resultSet.getMetaData().getColumnName(j);
String type = resultSet.getMetaData().getColumnTypeName(j);
//针对只有一次_的情况，多次则可用递归
if (name.contains("_")) {
int position = name.indexOf("_");
String smailChar = name.substring(position + 1, position + 2).toUpperCase();
name = name.substring(0, position) + smailChar + name.substring(position + 2, name.length());
}
Field declaredField = newClass.getDeclaredField(name);
if (declaredField != null) {
declaredField.setAccessible(true);
if (type.equals("INT"))
declaredField.set(o, resultSet.getInt(name));
else if (type.equals("VARCHAR"))
declaredField.set(o, resultSet.getString(name));
}
}
return (User) o;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
return null;
}
}
}
/**
* 测试代码
*/
public static void main(String[] args) {
Test01 test01 = (Test01) applicationContext.getBean("test01");
test01.test01();*/
TestDao dao = new TestDao();
dao.insertUser();
dao.updateUser();
dao.selectAllUser();
dao.selectUser();
dao.selectCountUser();
}
