1.JDBC 是什么
JDBC(JavaDataBase Connectivity)就是Java数据库连接,说白了就是用Java语言来操作数据库。原来我们操作数据库是在控制台使用SQL语句来操作数据库,JDBC是用Java语言向数据库发送SQL语句。
2.JDBC相关类与接口
DriverManager 驱动管理类
Connection 连接接口
Statement 语句对象接口
ResultSet 结果集接口
3.JDBC四要素
1.驱动 (驱动包)
2.url (数据库url)
3.用户名 (数据库用户名)
4.密码 (数据库密码)
4.JDBC操作数据库的步骤
1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
2.创建链接
Connection conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
3.创建Statement语句对象
Statement stat = conn.createStatement();
4.执行SQL
int ren = stat.executeUpdate(sql);
5.释放资源
ren.close();
stat.close();
conn.close();
举一个例子:
public class gan {
//查询数据
public List<teacher> findAllteacher() {
List<teacher> slist = null;
Connection conn = null;
Statement stat = null;
ResultSet rs =null;
try {
// 获取连接对象
conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/作业2?serverTimezone=GMT", "root","123456");
stat = conn.createStatement();
//执行SQL语句
rs = stat.executeQuery("select * from teacher");
if (rs != null) {
slist =new ArrayList<teacher>();
while(rs.next()) {
teacher t = new teacher();
t.setTid(rs.getInt("tid"));
t.setTname(rs.getString("tname"));
t.setTsex(rs.getInt("tsex"));
t.setTbirthday(rs.getDate("tbirthday"));
t.setTemail(rs.getNString("temail"));
t.setTaddress(rs.getNString("taddress"));
t.setTmoney(rs.getDouble("tmoney"));
slist.add(t);
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs != null) {
rs.close();
}
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return slist;
}
//添加数据
public boolean add(teacher t) {
SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
boolean a = false;
Connection conn = null;
Statement stat = null;
try {
conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/作业2?serverTimezone=GMT", "root","123456");
stat = conn.createStatement();
int rs= stat.executeUpdate
("insert into teacher(tname,tsex,taddress,temail,tmoney,tbirthday)
values('"+t.getTname()+"','"+t.getTsex()+"','"+t.getTaddress()+"','"+t.getTemail()+"','"+t.getTmoney()+"','"+sdf.format(t.getTbirthday())+"')");
if (rs > 0) {
a =true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return a;
}
//修改数据
public boolean update(teacher t) {
SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
boolean a = false;
Connection conn = null;
Statement stat = null;
try {
conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/作业2?serverTimezone=GMT", "root","123456");
stat = conn.createStatement();
int rs= stat.executeUpdate
("update teacher set tname='"+t.getTname()+"',tsex ='"+t.getTsex()+"',taddress ='"+t.getTaddress()+"',temail ='"+t.getTemail()+"',tmoney ='"+t.getTmoney()+"',tbirthday ='"+sdf.format(t.getTbirthday())+"'where tid = '"+t.getTid()+"'");
if (rs > 0) {
a =true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return a;
}
//删除数据
public boolean delete(teacher t) {
boolean a = false;
Connection conn = null;
Statement stat = null;
try {
conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/作业2?serverTimezone=GMT", "root","123456");
stat = conn.createStatement();
int rs= stat.executeUpdate
("delete from teacher where tid = '"+t.getTid()+"'");
if (rs > 0) {
a =true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(stat != null) {
stat.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return a;
}
}
总结:
使用JDBC要记住这五个步骤:
1. 加载数据库驱动
2. 创建连接 (可以使用连接池,就是把连接对象放进 一个集合,用于之后在放回集合中,连接信息可以放在一个属性文件中)
3. 创建语句对象(Preparedstatement 用的好 ,防止sql注入)
4.执行sql语句 (如果执行语句比较多的话,使用batch,还可以获取执行后的id),获取结果集 ,
5.关闭连接,释放 资源