1.添加信息
// 添加信息
@Test
public void testInsert(){
try{
Connection conn = MySqlDbUtil.getConnection();
String sql = "INSERT INTO band(name,remark) VALUES(?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"软工1班");
pstmt.setString(2,"备注信息");
int result = pstmt.executeUpdate();
if (result>0){
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
打开数据库可以看到添加的信息:
2.修改信息
// 修改信息
@Test
public void testUpdate(){
try{
Connection conn = MySqlDbUtil.getConnection();
String sql = "UPDATE band SET name=?,remark=? WHERE id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"软工2班");
pstmt.setString(2,"备注信息2");
pstmt.setInt(3,8);
int result = pstmt.executeUpdate();
if (result>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
3.删除信息
// 删除信息
@Test
public void testDelete(){
try{
Connection conn = MySqlDbUtil.getConnection();
String sql = "DELETE FROM band WHERE id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,8);
int result = pstmt.executeUpdate();
if (result>0){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
4.查询所有信息
// 查询所有信息
@Test
public void TestFindAll(){
try{
Connection conn = MySqlDbUtil.getConnection();
String sql = "SELECT * FROM band ";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String remark = rs.getString("remark");
System.out.println(id + ","+ name + "," + remark);
}
rs.close();
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
5.按字段值查询
@Test
public void TestFindById(){
try{
Connection conn = MySqlDbUtil.getConnection();
String sql = "SELECT * FROM band WHERE id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,10);
ResultSet rs = pstmt.executeQuery();
if (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String remark = rs.getString("remark");
System.out.println(id + ","+ name + "," + remark);
}
rs.close();
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
6.实现模糊查询
@Test
public void TestFindByName(){
try{
Connection conn = MySqlDbUtil.getConnection();
String sql = "SELECT * FROM band WHERE name LIKE ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"%1%");
ResultSet rs = pstmt.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String remark = rs.getString("remark");
System.out.println(id + ","+ name + "," + remark);
}
rs.close();
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}