目的:学会JDBC,参考自imooc的视频教程。
用到的工具Navicat for MySQL一个非常好的MySQL操作软件。注意事项:要记住MySQL安装时候的用户名和密码。
还有一点可能要注意的就是电脑上装的MySQL要处于运行状态,才去Run As Java Application。
用到mysql-connector-java-xxx-bin.jar这个驱动。
DEMO结构: 数据库imooc的表player的结构如下:
先用Navicat创建好数据库和表,以命令行形式。用命令行介面:
create database imooc;
create table player(
id int primary key auto_increment,
name varchar(30) not null,
age int,
birthday date
)engine=innodb default charset=utf8 auto_increment=1;
alter database testDB default character set 'utf8';
SET charater_set_client='utf8';
SET charater_set_connection='utf8';
SET charater_set_results='utf8';
ENGINE=InnoDB 表示将数据库的引擎设置为InnoDB,从MySQL 5.6开始默认使用该引擎。
DEFAULT CHARSET=utf8 表示设置数据库的默认字符集为utf8
AUTO_INCREMENT=1 表示自动增长的起始值为1
代码一: BDUtil.java ,连接数据库的类。其实应该命名为DBUtil才对==|||||
public class BDUtil {
private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc";
//3306为mysql端口号,imooc为数据库名字
private static final String USER="root";
private static final String PASSWORD="root";
public static Connection conn;
public BDUtil() { }
static{
try {
Class.forName("com.mysql.jdbc.Driver");
conn=(Connection) DriverManager.getConnection(URL,USER,PASSWORD);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
return conn;
}
public static void main(String[] args)throws Exception {
//这里的main()仅为了测试数据库能否连接正常而已
Statement stmt=(Statement) conn.createStatement();
String sql="select * from player";
stmt.executeQuery(sql);
//引入的是java.sql.ResultSet 不是驱动里的ResultSet
ResultSet rs=stmt.getResultSet();
Player p=null;
List<Player> pList=new ArrayList<Player>();
while(rs.next()){
System.out.println("name="+rs.getString("name")+"--age="+rs.getInt("age"));
}
}
}
代码二: Dao.java
public class Dao {
public Dao() {
}
public void addPlayer(Player player) throws SQLException{
Connection conn=BDUtil.getConnection();
String sql="insert into player "
+ "(name,age)"+"values(?,?)";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, player.getName());
ptmt.setInt(2, player.getAge());
ptmt.execute();
}
public void delPlayer(Integer id) throws SQLException{
Connection conn=BDUtil.getConnection();
String sql="delete from player"+
" where id=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setInt(1,id);
ptmt.execute();
}
public void updatePlayer(Player p) throws SQLException{
Connection conn=BDUtil.getConnection();
String sql="update player"+
" set name=?,age=? "+
"where id=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, p.getName());
ptmt.setInt(2, p.getAge());
ptmt.setInt(3,p.getId());
ptmt.execute();
}
//查询全部
public ArrayList<Player> query() throws SQLException{
Connection conn=BDUtil.getConnection();
Statement stmt=(Statement) conn.createStatement();
String sql="select * from player";
stmt.executeQuery(sql);
//引入的是java.sql.ResultSet 不是驱动里的ResultSet
ResultSet rs=stmt.getResultSet();
Player p=null;
ArrayList<Player> pList=new ArrayList<Player>();
while(rs.next()){
System.out.println("name="+rs.getString("name")+"--age="+rs.getInt("age"));
p =new Player();
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
pList.add(p);
}
return pList;
}
//根据ID查询单个
public Player get(Integer id) throws SQLException{
Connection conn=BDUtil.getConnection();
String sql="select * from player "
+ " where id=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setInt(1, id);
// ptmt.execute(); 增删改用execute() 查询用executeQuery();
ResultSet rs=ptmt.executeQuery();
Player p=null;
while(rs.next()){
p=new Player();
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
p.setId(rs.getInt("id"));
}
return p;
}
//根据名字查询,返回数组(重名情况)
public ArrayList<Player> query(String name) throws SQLException{
Connection conn=BDUtil.getConnection();
String sql="select * from player "
+" where name=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, name);//%为通配符
ResultSet rs=ptmt.executeQuery();
Player p=null;
ArrayList<Player> pList=new ArrayList<Player>();
while(rs.next()){
p=new Player();
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
p.setId(rs.getInt("id"));
pList.add(p);
}
return pList;
}
//自动匹配传入多个参数查询
public ArrayList<Player> query(List<Map<String,Object>>params) throws SQLException{
Connection conn=BDUtil.getConnection();
StringBuilder sb=new StringBuilder();
sb.append("select * from player where 1=1");//1=1是免得第一个也出现and
if(params!=null&¶ms.size()>0){
for(int i=0;i<params.size();i++){
Map<String,Object> map=params.get(i);
sb.append(" and "+map.get("name")+" "+map.get("rela")+map.get("value"));
//rela可以为各种通配符
}
}
PreparedStatement ptmt=conn.prepareStatement(sb.toString());
ResultSet rs=ptmt.executeQuery();
Player p=null;
ArrayList<Player> pList=new ArrayList<Player>();
while(rs.next()){
p=new Player();
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
p.setId(rs.getInt("id"));
pList.add(p);
}
return pList;
}
}
代码三: PlayAction.java ,演示类运行其中main()方法
public class PlayAction {
public PlayAction() {
}
public static void main(String[] args) throws SQLException {
Dao dao=new Dao();
// 查询
// List<Player> pList=dao.query();
// for(Player p:pList){
// System.out.println(p.getName()+"--"+p.getAge());
// }
// 添加
// Player p=new Player();
// p.setName("Odom");
// p.setAge(36);
// dao.addPlayer(p);
// 更新
// Player p=new Player();
// p.setName("Jeremy_Lin");
// p.setAge(28);
// p.setId(3);
// dao.updatePlayer(p);
// 删除
// dao.delPlayer(3);
// 查询单个
// Player p=dao.get(2);
// System.out.println(p.toString());
// 根据name查询
// ArrayList<Player> pList=dao.query("kobe");
// System.out.println(pList.size()+"");
// for(int i=0;i<pList.size();i++){
// Player p=pList.get(i);
// System.out.println(p.toString());
// }
// 多参数自动匹配查询
List<Map<String,Object>> params=new ArrayList<Map<String,Object>>();
Map<String,Object> param=new HashMap<String,Object>();
param.put("name", "name");
param.put("rela", "=");
param.put("value", "'kobe'");//记住这里必须加'',但为什么?_?
// param.put("name", "name");
// param.put("rela", "like");
// param.put("value", "'%o%'");//%为通配符
// param.put("age", "age");
// param.put("rela", "=");
// param.put("value", "'36'");//通过Age去查询
params.add(param);
ArrayList<Player> pList=dao.query(params);
System.out.println(pList.size()+"");
for(int i=0;i<pList.size();i++){
Player p=pList.get(i);
System.out.println(p.toString());
}
}
}
代码四: Player.java ,JavaBean类
public class Player {
private String name;
private int age;
private int id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Player() {
}
@Override
public String toString() {
return "name="+this.getName()+"--age="+this.getAge()+"--id="+this.getId();
}
}