package jdbc.my;
import com.mysql.jdbc.Connection;
import java.sql.DriverManager;
import com.mysql.jdbc.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
class Stu {
public Stu(int id, String sname, int sage) {
super();
this.id = id;
this.sname = sname;
this.sage = sage;
}
public Stu() {
super();
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
@Override
public String toString() {
return "Stu [id=" + id + ", sname=" + sname + ", sage=" + sage + "]";
}//如果不重写默认打印这个对象的地址
private int id;
private String sname;
private int sage;
}
public class JdbTestPs {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
String user="root";
String password="onno799967";
String url="jdbc:mysql://localhost:3306/mysql6";
//通过反射加载驱动
Class.forName("com.mysql.jdbc.Driver");
//建立连接
Connection conn=(Connection)DriverManager.getConnection(url, user, password);
//创建预编译使用的sql语句,参数用?占位
String insertSql="insert into stu(sname,sage) values(?,?);";
//获得prepareStatement对象,预编译sql语句
PreparedStatement ps=(PreparedStatement) conn.prepareStatement(insertSql);//参数为sql语句
//完善
ps.setString(1, "chen'10");//从下表1开始,表示第一个问号
ps.setInt(2, 40);
//执行提交sql语句
int i=ps.executeUpdate();
System.out.println("影响的数量"+i);
//修改
String updateSql ="update stu set sname=?,sage=? where id=?;";
//预编译Sql
ps=(PreparedStatement) conn.prepareStatement(updateSql);
ps.setString(1,"sun'12");
ps.setInt(2, 50);
ps.setInt(3, 5);
//执行sql
int j=ps.executeUpdate();
System.out.println("影响的数量"+j);
//删除
String deleteSql="delete from stu where id=?;";
ps=(PreparedStatement) conn.prepareStatement(deleteSql);
ps.setInt(1, 5);
int k=ps.executeUpdate();
System.out.println("影响的数量:"+k);
//查询
String querySql ="select id,sname,sage from stu where sage>?;";
ps=(PreparedStatement) conn.prepareStatement(querySql);
ps.setInt(1, 21);
//执行sql,获得结果集ResultSet对象,把数据封装成学生对象,需要一个同期收集
ArrayList<Stu> stus=new ArrayList<Stu>();
ResultSet rs=ps.executeQuery();
while(rs.next()) {
int id=rs.getInt("id");
String sname=rs.getString("sname");//获得名字字段的值
int sage=rs.getInt("sage");//获得年龄字段的值
System.out.println(sname+" "+sage);
//以java面向对象的思想,把从数据库中得到的数据封装成学生对象
Stu stu=new Stu(id,sname,sage);
//收集学生对象,一般用list不过滤重复,效率高,用这个是因为因为数据一般没什么重复
stus.add(stu);
//打印集合,查看结果
System.out.println(stus);
}
if(ps!=null)
ps.close();
if(rs!=null)
rs.close();
if(conn!=null)
conn.close();
}
}