项目中连接数据库，类的封装；
一.首先创建一个类，封装数据库和个人信息：
package com.yxq.dao; public class Constants { public static String url ="jdbc:mysql://127.0.0.1:3306/personblog"; public static String className="com.mysql.jdbc.Driver"; public static String userName ="root"; public static String password = "root"; }
以后只要修改这个类的信息；就可以了；
还要添加mysql-connector-java-5.0.8.jar 这个jar包到项目中；
先在网上下载这个jar，然后复制到项目WEB-INF目录下的lib文件夹中，在选中jar，右击，build path—> add bulid path;
二、封装一个类来连接数据库和关闭数据库：
package com.yxq.dao; /*导入所需的包*/ import java.sql.Connection;//表示链接到某个数据库的链接; import java.sql.DriverManager;//用来获取数据库链接的类; import java.sql.ResultSet;//封装查询结果集的类; import java.sql.SQLException; //import java.sql.SQLException;//异常处理类; import java.sql.Statement; //用来执行SQL语句的类; public class DB { // 获取数据的连接； public static Connection getConnection() throws Exception{ try{ Class.forName(Constants.className); Connection con = DriverManager.getConnection(Constants.url,Constants.userName,Constants.password); return con; }catch (Exception e) { e.printStackTrace(); System.out.println("连接失败"); return null; } } // 关闭Statement对象; public static void closeStatement(Statement stmt) throws Exception{ try{ if(stmt !=null){ stmt.close(); System.out.println("关闭Statement成功"); } }catch (Exception e) { e.printStackTrace(); System.out.println("关闭Statement失败"); } } // 关闭ResultSet对象; public static void closeResultSet(ResultSet rs) throws Exception{ try{ if(rs!=null){ rs.close(); System.out.println("关闭ResultSet成功"); } }catch (Exception e) { e.printStackTrace(); System.out.println("关闭ResultSet失败"); } } //关闭connection对象; public static void closeConnection(Connection conn,Statement stm,ResultSet rs)throws Exception{ try{ if(rs!= null){ rs.close(); System.out.println("关闭rs成功"); } if(stm != null){ stm.close(); System.out.println("关闭stm成功"); } if(conn!=null){ conn.close(); System.out.println("关闭Connection成功"); } }catch (Exception e) { e.printStackTrace(); System.out.println("关闭Connection失败"); } } }
三.创建一个特定的类，来增删改查数据里特定的一张表;
package com.yxq.dao; import java.sql.Connection; import java.sql.Date; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.yxq.valuebean.Model_Artical; import com.yxq.dao.DB; public class ArticalDao { /* * @Description: 查询文章; * @param :参数是start，end ,分别是数据的第几条数据，和一次查询的数量； * @return:返回查询的记录，封装在List中。 * */ public List<Model_Artical> query(int start,int end) throws Exception{ Connection con = DB.getConnection(); Statement stm = con.createStatement(); String sql="SELECT * FROM tbl_artical limit "+start+","+end+""; ResultSet rs= stm.executeQuery(sql); List<Model_Artical> model_artical = new ArrayList<Model_Artical>(); Model_Artical artical= null; while(rs.next()){ artical = new Model_Artical(); artical.setArtical_Id(rs.getInt(1)); artical.setArtical_Name(rs.getString("Artical_Name")); artical.setArtical_Content(rs.getString("Artical_Content")); artical.setArtical_Date(rs.getDate("Artical_Date")); artical.setArtical_Glancenumber(rs.getInt("Artical_Glancenumber")); artical.setUser_Id(rs.getInt("User_Id")); artical.setArticalTypel_Id(rs.getInt("ArticalTypel_Id")); artical.setArtical_Character(rs.getString("Artical_Character")); model_artical.add(artical); } DB.closeConnection(con,stm,rs); return model_artical; } /* * @Description: 查询文章; * @param :参数是typeid，start，end ,分别是文章的typeid值和数据的第几条数据，和一次查询的数量； * @return:返回查询的记录，封装在List中。 * */ public List<Model_Artical> querytype(int typeid,int start,int end) throws Exception{ Connection con = DB.getConnection(); Statement stm = con.createStatement(); String sql="SELECT * FROM tbl_artical where ArticalTypel_Id = '"+typeid+"' limit "+start+","+end+""; ResultSet rs= stm.executeQuery(sql); List<Model_Artical> model_artical = new ArrayList<Model_Artical>(); Model_Artical artical= null; while(rs.next()){ artical = new Model_Artical(); artical.setArtical_Id(rs.getInt(1)); artical.setArtical_Name(rs.getString("Artical_Name")); artical.setArtical_Content(rs.getString("Artical_Content")); artical.setArtical_Date(rs.getDate("Artical_Date")); artical.setArtical_Glancenumber(rs.getInt("Artical_Glancenumber")); artical.setUser_Id(rs.getInt("User_Id")); artical.setArticalTypel_Id(rs.getInt("ArticalTypel_Id")); artical.setArtical_Character(rs.getString("Artical_Character")); model_artical.add(artical); } DB.closeConnection(con,stm,rs); return model_artical; } /*创建用来实现查看文章详细内容的方法---queryArticleSingle()； * 该方法带有一个参数。表示文章的id* */ public Model_Artical queryArticleSingle(int id) throws Exception{ String sql = "select * from tbl_artical where Artical_Id = '"+id+"'"; Connection con = DB.getConnection(); Statement stm = con.createStatement(); ResultSet rs= stm.executeQuery(sql); try{ while(rs.next()){ Model_Artical artical = new Model_Artical(); artical.setArtical_Id(rs.getInt(1)); artical.setArtical_Name(rs.getString("Artical_Name")); artical.setArtical_Content(rs.getString("Artical_Content")); artical.setArtical_Date(rs.getDate("Artical_Date")); artical.setArtical_Glancenumber(rs.getInt("Artical_Glancenumber")); artical.setUser_Id(rs.getInt("User_Id")); artical.setArticalTypel_Id(rs.getInt("ArticalTypel_Id")); DB.closeConnection(con,stm,rs); return artical; } }catch(Exception e){ e.printStackTrace(); System.out.println("按文章类别查看文章失败"); DB.closeConnection(con,stm,rs); return null; } return null; } /* operationArtical()方法 实现对数据库进行其他的更新操作,如添加，修改和删除*/ public boolean operattionArtical(String oper,Model_Artical artical) throws Exception{ Connection con = DB.getConnection(); Statement stm = con.createStatement(); String sql = null; if(oper.equals("add")){ sql = "insert into tbl_artical (Artical_Name,Artical_Content,Artical_Date,Artical_Glancenumber,ArticalTypel_Id,User_Id,Artical_Character) values('"+artical.getArtical_Name()+"','"+artical.getArtical_Content()+"','"+new Date(artical.getArtical_Date().getTime())+"','"+artical.getArtical_Glancenumber()+"','"+artical.getArticalTypel_Id()+"','"+artical.getUser_Id()+"','"+artical.getArtical_Character()+"')"; int rs = stm.executeUpdate(sql); DB.closeConnection(con,stm,null); if( rs > 0 ){ return true; }else{ return false; } } return false; } /*创建一个方法是删除相应的文章*/ public boolean deleteartical(int articalId) throws Exception{ Connection con = DB.getConnection(); Statement stm = con.createStatement(); String sql="delete from tbl_artical where Artical_Id ="+articalId; int rs = stm.executeUpdate(sql); if( rs > 0 ){ DB.closeConnection(con,stm,null); System.out.println("删除成功"); return true; }else{ DB.closeConnection(con,stm,null); System.out.println("删除失败"); return false; } } /*创建一个修改相对应的文章*/ public boolean modifyarticle(int articleId,String articlecontent,String articlename,String character,int typeid) throws Exception{ Connection con = DB.getConnection(); Statement stm = con.createStatement(); String sql="UPDATE tbl_artical SET Artical_Content ='"+articlecontent+"',Artical_Name='"+articlename+"',Artical_Character ='"+character+"',ArticalTypel_Id="+typeid+" where Artical_Id ="+articleId; int rs = stm.executeUpdate(sql); if( rs > 0 ){ DB.closeConnection(con,stm,null); System.out.println("修改成功"); return true; }else{ DB.closeConnection(con,stm,null); System.out.println("修改失败"); return false; } } /* * @Description: 计算某一类文章或者是全部文章的总数; * @param :参数是typeid，判断是某一类文章，还是数据库里的全部文章， * @return:返回数量； * */ public int articalnum(int typeid) throws Exception{ String sql; if(typeid > 0){ sql = "SELECT count(*) FROM tbl_artical where ArticalTypel_Id = '"+typeid+"'"; }else{ sql="SELECT count(*) FROM tbl_artical"; } Connection con = DB.getConnection(); Statement stm = con.createStatement(); ResultSet rs= stm.executeQuery(sql); int i = 0; while(rs.next()){ i = rs.getInt(1); } DB.closeConnection(con,stm,rs); return i; } }
