在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;
}
以上功能都是业务代码,仅做参考!