import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Zy3 {
static Connection con;
static PreparedStatement sql;
static ResultSet res;
public Connection getConnection() {
try{
Class.forName("com.mysql.jdbc.Driver");
con =DriverManager.getConnection("jdbc:mysql:"+"//127.0.0.1:3306/phone_book", "root", "123456");
}catch (Exception e){
e.printStackTrace();
}
return con;
}
public static void main(String[] args) {
Zy3 c= new Zy3();
con= c.getConnection();
try{
sql=con.prepareStatement("select*from info");
res=sql.executeQuery();
System.out.println("查询通讯录信息:");
while (res.next()){
String id=res.getString("id");
String name =res.getString("name");
String tel =res.getString("tel");
String sex =res.getString("sex");
System.out.print("学号:"+id);
System.out.print(" 姓名:"+name);
System.out.print(" 电话号码:"+tel);
System.out.println(" 性别:"+sex);
}
System.out.println("------------------------------------------");
sql=con.prepareStatement("insert into info "+ "values(?,?,?,?)");
sql.setString(1, "111");
sql.setString(2, "萧丽");
sql.setString(3, "15047890635");
sql.setString(4, "女");
sql.executeUpdate();
sql.setString(1, "112");
sql.setString(2, "小刘");
sql.setString(3, "13889048394");
sql.setString(4, "男");
sql.executeUpdate();
sql.setString(1, "113");
sql.setString(2, "小胡");
sql.setString(3, "15047856925");
sql.setString(4, "女");
sql.executeUpdate();
System.out.println("增加后的数据");
sql=con.prepareStatement("select*from info");
res=sql.executeQuery();
while (res.next()){
String id=res.getString("id");
String name =res.getString("name");
String tel =res.getString("tel");
String sex =res.getString("sex");
System.out.print("学号:"+id);
System.out.print(" 姓名:"+name);
System.out.print(" 电话号码:"+tel);
System.out.println(" 性别:"+sex);
}
System.out.println("------------------------------------------");
System.out.println("查询182开头的信息");
sql=con.prepareStatement("select * from info where "+"tel like '182%'");
res=sql.executeQuery();
while (res.next()){
String id=res.getString("id");
String name =res.getString("name");
String tel =res.getString("tel");
String sex =res.getString("sex");
System.out.print("学号:"+id);
System.out.print(" 姓名:"+name);
System.out.print(" 电话号码:"+tel);
System.out.println(" 性别:"+sex);
}
System.out.println("------------------------------------------");
System.out.println("更改第五的电话数据");
sql=con.prepareStatement("update info set tel"+"=? where id =105");
sql.setString(1, "12345678901");
sql.executeUpdate();
System.out.println("------------------------------------------");
System.out.println("查询男性的信息");
sql=con.prepareStatement("select * from info where "+"sex = '男'");
res=sql.executeQuery();
while (res.next()){
String id=res.getString("id");
String name =res.getString("name");
String tel =res.getString("tel");
String sex =res.getString("sex");
System.out.print("学号:"+id);
System.out.print(" 姓名:"+name);
System.out.print(" 电话号码:"+tel);
System.out.println(" 性别:"+sex);
}
System.out.println("------------------------------------------");
System.out.println("更改第10的姓名");
sql=con.prepareStatement("update info set name"+"=? where id =110");
sql.setString(1, "大帅哥");
sql.executeUpdate();
System.out.println("------------------------------------------");
System.out.println("查询182开头的信息");
sql=con.prepareStatement("select * from info where "+"tel like '182%'");
res=sql.executeQuery();
while (res.next()){
String id=res.getString("id");
String name =res.getString("name");
String tel =res.getString("tel");
String sex =res.getString("sex");
System.out.print("学号:"+id);
System.out.print(" 姓名:"+name);
System.out.print(" 电话号码:"+tel);
System.out.println(" 性别:"+sex);
}
System.out.println("------------------------------------------");
System.out.println("删除第3个数据");
sql.executeUpdate("delete from info where id =103");
System.out.println("------------------------------------------");
System.out.println("查询女性的信息");
sql=con.prepareStatement("select * from info where "+"sex = '女'");
res=sql.executeQuery();
while (res.next()){
String id=res.getString("id");
String name =res.getString("name");
String tel =res.getString("tel");
String sex =res.getString("sex");
System.out.print("学号:"+id);
System.out.print(" 姓名:"+name);
System.out.print(" 电话号码:"+tel);
System.out.println(" 性别:"+sex);
}
System.out.println("------------------------------------------");
sql=con.prepareStatement("select*from info");
res=sql.executeQuery();
System.out.println("查询修改后的数据:");
while (res.next()){
String id=res.getString("id");
String name =res.getString("name");
String tel =res.getString("tel");
String sex =res.getString("sex");
System.out.print("学号:"+id);
System.out.print(" 姓名:"+name);
System.out.print(" 电话号码:"+tel);
System.out.println(" 性别:"+sex);
}
}catch (Exception e){
e.printStackTrace();
}finally{
if(res !=null){
try{
res.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(con !=null){
try{
con.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(sql !=null){
try{
sql.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}
}