查询:
连接数据库:
public static Connection getconnection(){
Connection con = null;
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306/student"; //数据库连接字符串
String username="root";//用户名称
String password ="root";//用户密码
con = DriverManager.getConnection(url, username, password);//连接数据库
if(con!=null){
System.out.println("连接成功");
}else{
System.out.println("连接失败");
}
} catch (SQLException e) {
e.printStackTrace();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
return con;
}
业务:
public class ShuJUTest {
@Test
public void add(){
//连接数据库
Connection conn=LianJie.getconnection();
String sql = "select * from student";
try {
//操作数据库 静态执行
Statement st = conn.createStatement();
//操作数据库返回结果
ResultSet rs = st.executeQuery(sql);
if(rs!=null){
//循环resultset
while (rs.next()) {
// 得到他的值
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
修改:
@Test
public void xiugai(){
Connection conn =LianJie.getconnection();
try {
String sql ="UPDATE student set name=? WHERE id=?";
//动态执行sql 语句
PreparedStatement sm = conn.prepareStatement(sql);
sm.setString(1,"yuntaos");
sm.setInt(2,2);
//执行添加
int ter = sm.executeUpdate();
if(ter!=0){
System.out.println("成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
添加:
//添加
@Test
public void adds(){
Connection conn = LianJie.getconnection();
String sql ="INSERT INTO student(id,name,classname) VALUES(?,?,?)";
try {
//执行sql语句动态
PreparedStatement ps =conn.prepareStatement(sql);
//给赋值
ps.setInt(1,11);
ps.setString(2,"ssss");
ps.setString(3,"444");
//执行update
int ter =ps.executeUpdate();
if(ter!=0){
System.out.println("添加成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
删除:
@Test
public void delete(){
Connection conn = LianJie.getconnection();
String sql ="delete FROM student where id=8";
try {
//执行sql语句动态
PreparedStatement ps =conn.prepareStatement(sql);
//执行update
int ter =ps.executeUpdate();
if(ter!=0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
批量处理:
添加:
@Test
public void adds1(){
Connection conn = LianJie.getconnection();
String sql ="INSERT INTO student(id,name,classname) VALUES(?,?,?)";
try {
//执行sql语句动态
PreparedStatement ps =conn.prepareStatement(sql);
for(int i =9;i<13;i++){
ps.setInt(1,i);
ps.setString(2,"yuntao"+i);
ps.setString(3,"ss"+i);
ps.addBatch();
}
//执行update
ps.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
}
}