public void testDruid() throws SQLException{//原始连接池
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/jdbc");
dataSource.setUsername("root");
dataSource.setPassword("root");
DruidPooledConnection connection = dataSource.getConnection();
String sql ="select * from users";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String pwd = resultSet.getString(3);
String email = resultSet.getString(4);
System.out.println("id是"+id+"名字是"+name+"pass"+pwd+"email"+email);
}
@Test//增,删,改,查
public void testZong() throws Exception{
Properties properties = new Properties();
properties.load(DruidDemo2.class.getClassLoader().getResourceAsStream("alibaba.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
String sql ="insert into users values(?,?,?,?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 4);
statement.setString(2, "hh");
statement.setString(3, "666666");
statement.setString(4, "hh@sina.com");
statement.executeUpdate();
String sql1 = "update users set id=5 where name ='ff'";
PreparedStatement statement2 = connection.prepareStatement(sql1);
statement2.executeUpdate();
String sql3 ="delete from users where id =3";
PreparedStatement statement3 = connection.prepareStatement(sql3);
statement3.executeUpdate();
ResultSet resultSet = statement.executeQuery("select * from users");
while (resultSet.next()) {
String id = resultSet.getString(1);
String name = resultSet.getString(2);
String pwd = resultSet.getString(3);
String email = resultSet.getString(4);
System.out.println("id:"+id+"\tname"+name+"\tpwd"+pwd+"\temail"+email);
}
}
简单遍历
public static void main(String[] args) throws Exception {
//测试
DruidDemo03 druidDemo03 = new DruidDemo03();
Connection connect = druidDemo03.connect();
druidDemo03.add(10, "abc", "mima", "abc.@qq.com", "2018-07-24", connect);
//druidDemo03.update(6, "ABC", connect);
// druidDemo03.del(7, connect);
}
//获取连接
public Connection connect() throws Exception{
Properties properties = new Properties();
properties.load(DruidDemo2.class.getClassLoader().getResourceAsStream("alibaba.properties"));
DruidDataSource dataSource =(DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
return connection;
}
//关闭连接
public void close(Connection conn,PreparedStatement pre){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
pre.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//增
public void add(int i,String name,String password,String email,String date,Connection conn) throws SQLException{
String sql ="insert into users values (?,?,?,?,?)";
PreparedStatement statement = conn.prepareStatement(sql);
logger.info("执行的SQL为:{},参数为:ID={},name={},password={},email={},date={}",sql,i,name,password,email,date);
statement.setInt(1,i);
statement.setString(2, name);
statement.setString(3, password);
statement.setString(4, email);
statement.setString(5,date);
int j = statement.executeUpdate();
System.out.println("成功增加"+j+"一条数据");
}
//删
public void del(int i,Connection conn) throws SQLException{
String sql ="delete from users where id = ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setInt(1,i);
int j = statement.executeUpdate();
System.out.println("成功删除"+j+"一条数据");
}
//改
public void update(int i,String name,Connection conn) throws SQLException{
String sql ="update users set name = ? where id = ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, name);
statement.setInt(2,i);
int j = statement.executeUpdate();
System.out.println("成功修改"+j+"一条数据");
}
}