使用SQLl连接MyEclipse

学习内容：

1.使用SQLl连接MyEclipse

使用mysql-jar包导入lib以及database放入目录下


com.bdqn.entity参考代码：

public class Dept implements Serializable{
/*根据SQL里面的表数据创建相应属性并序列化并提供getset方法*/
private int did;
private String dname;
private String mdesc;
public int getDid() {
return did;
}
public void setDid(int did) {
this.did = did;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getMdesc() {
return mdesc;
}
public void setMdesc(String mdesc) {
this.mdesc = mdesc;
}
}

com.bdqn.dao参考代码：

BaseDao类如下：

public class BaseDao {
/*创建属性*/
private Connection conn;
private static String driver;
private static String url;
private static String user;
private static String password;

static{
/*静态代码块*/
init();
}

private static void init(){
//读取配置文件
Properties pro = new Properties();
//拿到配置文件的路径
String path ="database.properties";
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream(path);
try {
pro.load(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
driver = pro.getProperty("driver");
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
}

//获取对象
public Connection getConnection(){
try {
//通过Class.forName静态方法加载mysql驱动
Class.forName("com.mysql.jdbc.Driver");
//通过SQL包里DriverManager.getConnection获取SQL数据库的信息
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8", "root","root");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}

//增删改

public int update(String sql,Object[] objects){
int num = 0;
try {
conn = getConnection();
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]);
}
}
num = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return num;
}

//查询
public ResultSet getResultSet(String sql,Object[] objects){
ResultSet  rs = null;
conn = getConnection();
try {
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 connection ,PreparedStatement ps , ResultSet  rs){

try {
if(connection !=null){
connection.close();
}
if(ps !=null){
ps.close();
}
if(rs !=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

DeptDao类如下：

public interface DeptDao {

//查询
List<Dept> selectDept(String name);

//增加
int addDept(Dept dept);

//修改
int updateDept(Dept dept);

//删除
int delectDept(int id);
}

com.bdqn.dao.impl参考代码:

public class DeptDaoImpl extends BaseDao implements DeptDao{
/*编辑SQL语句传输相应指令*/
public List<Dept> selectDept(String name) {
List <Dept> list = new ArrayList<Dept>();
String sql = "select * from dept where dname=?";
try {
Object[] objects={name};
ResultSet rs = this.getResultSet(sql, objects);
while (rs.next()) {
Dept dept  = new Dept();
dept.setDid(rs.getInt("did"));
dept.setDname(rs.getString("dname"));
dept.setMdesc(rs.getString("mdesc"));
list.add(dept);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}


public int addDept(Dept dept) {
String sql ="insert into dept values(?,?,?)";
Object[] objects = {dept.getDid(),dept.getDname(),dept.getMdesc()};
     int num = this.update(sql, objects);
return num;
}


public int updateDept(Dept dept) {
String sql ="update dept set dname=?,mdesc=? where did=?";
Object[] objects = {dept.getDname(),dept.getMdesc(),dept.getDid()};
     int num = this.update(sql, objects);
return num;
}


public int delectDept(int id) {
String sql ="delete  from dept where did=?";
Object[] objects = {id};
     int num = this.update(sql, objects);
return num;
}
}

com.bdqn.text参考代码:

public class Text {
public static void main(String[] args) {
/*测试类*/
DeptDao deptDao  = new DeptDaoImpl();
Dept dept = new Dept();
dept.setDid(4);
dept.setDname("fgggf");
dept.setMdesc("gdgdgd");
int num =deptDao.delectDept(3);
System.out.println(num);
}
}