1、写SQL语句
a、create database phone_book;
use phone_book;
b、create table info (id char,name char(8),tel char(11),sex char(2));
c、insert into info values (‘1’,‘艾琳’,‘18702550410’,‘女’);
insert into info values (‘2’,‘老一’,‘18702550411’,‘男’);
insert into info values (‘3’,‘老二’,‘18202440010’,‘男’);
insert into info values (‘4’,‘老三’,‘13906550410’,‘男’);
insert into info values (‘5’,‘老四’,‘18202558910’,‘男’);
insert into info values (‘6’,‘老五’,‘18272550410’,‘男’);
insert into info values (‘7’,‘老六’,‘18702550410’,‘男’);
insert into info values (‘8’,‘小花’,‘13909550410’,‘女’);
insert into info values (‘9’,‘小林’,‘13922550410’,‘女’);
insert into info values (‘10’,‘冰冰’,‘18207550410’,‘女’);
2、Java代码
图中所有查询都可使用以下代码:
public static void main(String[] args) throws SQLException {
getconn();
String selectsql="select * from info";
(所有查询代码为:
select * from info where tel like ‘?%’;
select * from info where sex=?;
select * from info where tel like ‘?%’;
select * from info where sex=?;
)
//参数有几个问号就写几个
Object[] obj = {};
select(selectsql,obj);
demonext();
democlose();
}
private static void democlose() throws SQLException {
resultSet.close();
pstat.close();
conn.close();
}
private static void demonext() throws SQLException {
while (resultSet.next()){
System.out.println(“id=”+resultSet.getString(“id”)
+" name="+resultSet.getString(“name”)
+" tel="+resultSet.getString(“tel”)
+" sex="+resultSet.getString(“sex”));
}
}
private static void select(String selectsql,Object[] obj) throws SQLException {
pstat = conn.prepareCall(selectsql);
for (int i = 0; i < obj.length; i++) {
pstat.setObject(i+1,obj[i]);
}
resultSet =pstat.executeQuery();
}
增删改可以使用:
public static void main(String[] args) throws SQLException {
getconn();
String sql="insert into info values (?,?,?,?)";
(
update info set tel=? where id=?;//12345678901 5
update info set name=? where id=?;//大帅哥 10
delete from info where id=?;//3
)
Object[] obj = {"","","",""};
operat(sql,obj);
democlose();
}
private static void democlose() throws SQLException {
pstat.close();
conn.close();
}
private static void operat(String sql,Object[] obj) throws SQLException {
pstat = conn.prepareStatement(sql);//sql语句有参数变化就要用这个
for (int i = 0; i < obj.length; i++) {
pstat.setObject(i+1,obj[i]);
}
pstat.executeUpdate();
}
private static void getconn() throws SQLException {
conn = DriverManager.getConnection(“jdbc:mysql:/phone_book”, “root”, “123456”);
}