jdbc链接数据库

在MVC模式中javabean需要连接数据库完成持久化操作，这里和大家分享几条数据库链接和简单操作

1.用connection链接数据库

package it.com.conn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbConn {
	//链接数据库
	public Connection getCon(){
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/luntan";
			String user = "root";
			String pwd ="mysql";
			conn=DriverManager.getConnection(url, user, pwd);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	//关闭数据库
	public void closeConn(){
		Connection con = getCon();
		try {
			if(!con.isClosed()){
				con.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args){
		DbConn db = new DbConn();
		db.getCon();
		db.closeConn();
	}

}

2.一条通用的方法可以实现任意查询

public List<Map> selectEverything(String sql){
		DbConn db = new DbConn();
		Connection con = db.getCon();
		List<Map> list = new ArrayList<Map>();
		try {
			PreparedStatement pst=con.prepareStatement(sql);
			ResultSetMetaData rm = pst.getMetaData();
			int m = rm.getColumnCount();
			ResultSet rs = pst.executeQuery();
			while (rs.next()){
				Map map1 = new HashMap();
				
				for(int i=1;i<=m;i++){
					String col=rm.getColumnName(i);
					map1.put(col, rs.getString(i));
				}
				list.add(map1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn();
		}
		return list;
	}

3.向数据库中增加数据

//注册新用户,用int判断成功与否
	public int register(String userName,String userPwd,String Ename,String Email,String Logo){
		DbConn db = new DbConn();
		Connection con = db.getCon();
		int n=0;
		String sql="INSERT INTO users(userName,userPwd,Ename,Email,Logo) VALUES (?,?,?,?,?) ";
		try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setString(1, userName);
			pst.setString(2, userPwd);
			pst.setString(3, Ename);
			pst.setString(4, Email);
			pst.setString(5, Logo);
			pst.executeUpdate();
			n=1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn();
		}
		return n;
	}

4.删除数据

//按用户名删除用户信息，用int判断成功与否
	public int deleteByName(String userName){
		DbConn db = new DbConn();
		Connection con = db.getCon();
		int n=0;
		String sql="delete from users where userName = ?";
		try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setString(1, userName);
			pst.executeUpdate();
			n=1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn();
		}
		return n;
	}

5.更新数据

public void isTop(String contid,String istop){
		DbConn db = new DbConn();
		Connection con = db.getCon();
		String sql="update content set IsTop='"+istop+"' where ContId='"+contid+"'";
		try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn();
		}
	}

6.最初版本的查询（map可以换成实体类）

 public List<Map> getlist(String name){
		   List<Map> list =new ArrayList();
		   DbConn db = new DbConn();
			Connection con = db.getCon();
			String sql="SELECT * FROM content WHERE UserName =?  AND UpperId=0";
		    try
		    {
				PreparedStatement pp= con.prepareStatement(sql);
				pp.setString(1, name);
				 ResultSet rr= pp.executeQuery();
				 while(rr.next())
				 {
					 Map map=new HashMap();
					 map.put("ContId", rr.getString(1));
					 map.put("Subject", rr.getString(2));
					 map.put("Words", rr.getString(3));
					 map.put("UserName", rr.getString(4));
					 map.put("CreateTime", rr.getString(5));
					 map.put("HitCont", rr.getString(6));
					 map.put("IsTop", rr.getString(7));
					 map.put("UpperId", rr.getString(8));
					 list.add(map);
				 }
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		   
		   return list;
	   }

以上功能都是业务代码，仅做参考！