jdbc(java database connection)
接口是一种约束,不用管内部的实现,只需要调用其方法
特点:面向接口编程(api接口 statement),面向数据库编程
jdbc连接数据库的步骤“
第一步 加载数据库的驱动包(通过反射来加载)
第二步 获取数据库的连接
第三步 执行相应的操作
第四步 关闭资源
不分层的增删改查
package com.zhonggong.text;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args){
String diver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/student";
String user="root";
String password="123";
/*
* url :jdbc:+数据库类型+://+ip地址+:+端口号+/+访问的数据库的库名
*
* try catch快捷键 : shift+alt+z
*/
try {
//通过反射加载驱动
Class.forName(diver);
//管理器获取连接对象,第一个参数是获取数据库的地址,第二个参数是数据库用户名,第三个参数是数据库密码
Connection conn=DriverManager.getConnection(url, user, password);
System.out.println(conn);
/*statement执行sql语句的对象
*缺点:拼接麻烦,存在SQL语言的侵入
*
*一般不用statement,用其子类 PreparedStatement
*
*PreparedStatement ps=conn.prepareStatement()
*
* */
Statement sta=conn.createStatement();
String sql="select * from grade";
//获取结果集对象
ResultSet rs=sta.executeQuery(sql);
//rs.next()指针迭代是否有下一行
while(rs.next()){
//GradeId、GradeName 对应数据库的属性
int gradeId=rs.getInt("GradeId");
String gradeName=rs.getString("GradeName");
System.out.println(gradeId+"\t"+gradeName);
}
//修改表,返回值为影响的行数
String sql1="delete from grade where GradeId=1";
int num=sta.executeUpdate(sql1);
System.out.println(num);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
三层架构:对应的设计模式:mvc (model view controller)
好处:分层思想 使代码结构更加清晰化,便于修改,有利于后期项目的扩展,避免冗余代码
缺点:代码量增大
dao(用于连接数据库)
service(用于逻辑控制)
jsp(视图的显示)
DAO层案例如下,注意根据不同数据库软件要导入不同的jar包,jar包放入WebRoot/WEB-INF/lib中,
BaseDao.java
package com.zhonggong.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
String diver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/student";
String user="root";
String password="123";
private Connection conn;
//获取连接
public Connection getConnection() {
try {
//通过反射加载驱动
Class.forName(diver);
//获取连接对象
conn=DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//SQL 语句 delete from student where id=?;
//增删改
public int execUpdate(String sql,Object[] objects){
int num=-1;
PreparedStatement ps=null;//ps为获取执行sql的对象声明变量记得赋值!!!
try {
//获取连接对象
conn=getConnection();
//获取执行sql的对象
ps=conn.prepareStatement(sql);
//填充参数
if(objects!=null && objects.length>0){
for(int i=0;i<objects.length;i++){
//替代占位符
ps.setObject((i+1), objects[i]);
}
}
//执行修改等操作
num=ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
close(conn,ps,null);
}
return num;
}
//查询
public ResultSet getResultSet(String sql,Object[] objects){
ResultSet rs=null;
try {
//获取连接对象
conn=getConnection();
//执行sql的对象
PreparedStatement ps=conn.prepareStatement(sql);
//填充参数
if(objects!=null && objects.length>0){
for(int i=0;i<objects.length;i++){
//替代占位符
ps.setObject((i+1), objects[i]);
}
}
//提交数据库 //获取结果集对象
rs=ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
//关闭资源,从后往前关
public void close(Connection conn,PreparedStatement ps,ResultSet rs){
try {
if(rs !=null){
rs.close();
}
if(ps !=null){
ps.close();
}
if(conn !=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
GradeDao.java //为一个接口类
package com.zhonggong.dao;
import java.util.List;
import com.zhonggong.entity.Grade;
/*
* 接口里都是抽象方法,没有方法体
*
*
* */
public interface GradeDao {
//根据id查询
List<Grade> allGradeById(int gradeId);
//根据id删除
//根据id修改
int updateGrade(Grade grade);
//增加
int addGrade(Grade grade);
//获取总记录数
int selectCount();
}
GradeDaoImpl.jaca 为GradeDao.java 的实现类
package com.zhonggong.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.zhonggong.dao.BaseDao;
import com.zhonggong.dao.GradeDao;
import com.zhonggong.entity.Grade;
public class GradeDaoImpl extends BaseDao implements GradeDao{
@Override
public List<Grade> allGradeById(int gradeId) {
List<Grade> gradesList=new ArrayList<Grade>();
//sql,objects
try {
String sql="select * from grade where GradeID=?";
Object[] objects={gradeId};
// 调用BaseDao里查询的方法
ResultSet rs=this.getResultSet(sql, objects);
//得到数据,指针指向下一行
while(rs.next()){
Grade grade=new Grade();
grade.setGradeId(rs.getInt("GradeID"));
grade.setGradeName(rs.getString("gradeName"));
//把对象添加到集合
gradesList.add(grade);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return gradesList;
}
@Override
public int updateGrade(Grade grade) {
String sql="update grade set gradeName=? where gradeID=?";
Object[] objects={grade.getGradeName(),grade.getGradeId()};
int num= this.execUpdate(sql, objects);
return num;
}
@Override
public int addGrade(Grade grade) {
String sql="insert into grade values(?,?)";
Object[] objects={grade.getGradeId(),grade.getGradeName()};
int num= this.execUpdate(sql, objects);
return num;
}
@Override
public int selectCount() {
int num=-1;
try {
String sql="select * from grade";
ResultSet rs=this.getResultSet(sql, null);
rs.last();
num=rs.getRow();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return num;
}
}
创建的实体类Grade.java
package com.zhonggong.entity;
import java.io.Serializable;
public class Grade implements Serializable {
private int gradeId;
private String gradeName;
public int getGradeId() {
return gradeId;
}
public void setGradeId(int gradeId) {
this.gradeId = gradeId;
}
public String getGradeName() {
return gradeName;
}
public void setGradeName(String gradeName) {
this.gradeName = gradeName;
}
}
功能测试类
package com.zhonggong.text;
import java.util.List;
import com.zhonggong.dao.GradeDao;
import com.zhonggong.dao.impl.GradeDaoImpl;
import com.zhonggong.entity.Grade;
public class test1 {
public static void main(String[] args) {
GradeDao gradeDao=new GradeDaoImpl();
/*执行查询代码
* List<Grade> list=gradeDao.allGradeById(3);
for(Grade g:list){
System.out.println("编号"+g.getGradeId()+"\t"+"名字"+g.getGradeName());
}*/
/*
//执行修改
Grade grade=new Grade();
grade.setGradeId(3);
grade.setGradeName("java-1");
int num=gradeDao.updateGrade(grade);
System.out.println(num);
*/
/*//添加信息
Grade grade=new Grade();
grade.setGradeId(1);
grade.setGradeName("heihei");
int num=gradeDao.addGrade(grade);
System.out.println(num);
*/
//获取总记录数
int num=gradeDao.selectCount();
System.out.println(num);
}
}