数据库的几个连接语句
1.Mysql数据库
StringDriver="com.mysql.jdbc.Driver";
String URL="jdbc:mysql://localhost:3306/db_name";[W用1]
2.SqlServer数据库
StringDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
String URL="jdbc:sqlserver://localhost:1433;DatabaseName=db_name";[W用2]
常用sql语句
1.插入语句
insertinto tb_user(uname,upass)values('3333',123);
2.查询语句
select* from tb_user;
select uname,upassfrom tb_user;
3.删除语句
deletefrom tb_user whereuid=1;
truncatetable tb_user;
drop table tb_user;
4.更新语句
update tb_userset uname='战神七'where uid=2;
update tb_userset uname='痛苦女王',upass=123where uid=1;
update tb_userset uname='kusy';
数据库的连接(终极版)
连接数据库的工具类(SqlServer)
packagecom.softeem.db;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
publicclassDBConnection{
/**
*连接数据库的工具类
*/
privatestaticfinalStringdriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
privatestaticfinalStringurl="jdbc:sqlserver://localhost:1433;DatabaseName=test";
privatestaticfinalStringuser="sa";
privatestaticfinalStringpwd="123456";
privatestaticConnectionconn=null;
static{
try{
Class.forName(driver);
}catch(ClassNotFoundExceptione){
e.printStackTrace();
}
}
publicstaticConnectiongetConn(){
if(conn==null){
try{
conn=DriverManager.getConnection(url,user,pwd);
}catch(SQLExceptione){
e.printStackTrace();
}
}
returnconn;
}
publicstaticvoidgetClose(Connectionconn,ResultSetrs,
PreparedStatementps){
try{
if(conn!=null){
conn.close();
}
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
User对象类(赋值获值)
packagecom.softeem.dto;
publicclassUser{
/*
* 一个DTO对应的是一张数据库中的表,字段的数目和类型是和数据库的这张表示相对应的添加一条数据,就是添加一个DTO对象
*/
privateintuid;
privateStringuname;
privateStringupass;
publicUser(){
super();
}
publicUser(intuid,Stringuname,Stringupass){
super();
this.uid=uid;
this.uname=uname;
this.upass=upass;
}
publicintgetUid(){
returnuid;
}
publicvoidsetUid(intuid){
this.uid=uid;
}
publicStringgetUname(){
returnuname;
}
publicvoidsetUname(Stringuname){
this.uname=uname;
}
publicStringgetUpass(){
returnupass;
}
publicvoidsetUpass(Stringupass){
this.upass=upass;
}
}
UserDao接口
packagecom.softeem.dao;
importjava.util.List;
importcom.softeem.dto.User;
publicinterfaceUserDao{
/*
* 接口:所有方法的汇集方法没有方法体
*/
publicbooleanaddUser(Useruser);
publicbooleandelUser(intuid);
publicbooleanupdateUser(Useruser);
publicList<User>listUser();
publicUsergetUserById(intuid);
}
实现UserDao接口中的实现类(UserDaoImpl)
packagecom.soffteem.daoimpl;
importcom.softeem.dao.UserDao;
importcom.softeem.db.DBConnection;
importcom.softeem.dto.User;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.ArrayList;
importjava.util.List;
publicclassUserDaoImplimplementsUserDao{
/*
* 实现类:实现接口重写接口中的所有方法
*/
privateConnectionconn;
privateResultSetrs;
privatePreparedStatementps;
privateStatementst;
// 添加数据
publicbooleanaddUser(Useruser){
booleanflag=false;
try{
conn=DBConnection.getConn();
Stringsql="insert into tb_user(uname,upass) values(?,?)[W用1] ";
ps=conn.prepareStatement(sql);//获取预处理命令
ps.setString(1,user.getUname());//为问号赋值
ps.setString(2,user.getUpass());
inti=ps.executeUpdate();
if(i>0){
System.out.println("添加成功");
returntrue;
}
}catch(SQLExceptione){
e.printStackTrace();
}finally{
DBConnection.getClose(conn,rs,ps);
}
returnflag;
}
// 删除数据
publicbooleandelUser(intuid){
booleanflag=false;
try{
conn=DBConnection.getConn();
Stringsql="delete from tb_user where uid=?[W用2] ";
ps=conn.prepareStatement(sql);
ps.setInt(1,uid);
booleanb=!ps.execute();
if(b){
System.out.println("删除成功!");
returntrue;
}
}catch(SQLExceptione){
e.printStackTrace();
}finally{
DBConnection.getClose(conn,rs,ps);
}
returnflag;
}
// 根据id查询
publicUsergetUserById(intuid){
Useruser=null;
try{
conn=DBConnection.getConn();
Stringsql="select * from tb_user where uid=?[W用3] ";
ps=conn.prepareStatement(sql);
ps.setInt(1,uid);
rs=ps.executeQuery();
while(rs.next()){
intid=rs.getInt("uid");
Stringname=rs.getString("uname");
Stringpass=rs.getString("upass");
user=newUser(id,name,pass);
}
}catch(SQLExceptione){
e.printStackTrace();
}finally{
DBConnection.getClose(conn,rs,ps);
}
returnuser;
}
// 查询所有查询表查到的肯定是很多条记录
publicList<User>listUser(){
List<User>list=newArrayList<User>();
try{
conn=DBConnection.getConn();
Stringsql="select* from tb_user";
st=conn.createStatement();
rs=st.executeQuery(sql);
while(rs.next()){
intid=rs.getInt("uid");
Stringname=rs.getString("uname");
Stringpass=rs.getString("upass");
Useruser=newUser(id,name,pass);
list.add(user);[W用4]
}
}catch(SQLExceptione){
e.printStackTrace();
}finally{
DBConnection.getClose(conn,rs,ps);
}
returnlist;
}
publicbooleanupdateUser(Useruser){
booleanflag=false;
try{
conn=DBConnection.getConn();
Stringsql="update tb_user set uname=?,upass=? where uid=?[W用5] ";
ps=conn.prepareStatement(sql);
ps.setString(1,user.getUname());
ps.setString(2,user.getUpass());
ps.setInt(3,user.getUid());
booleanb=!ps.execute();
if(b){
System.out.println("更新数据成功!");
returntrue;
}
}catch(SQLExceptione){
e.printStackTrace();
}finally{
DBConnection.getClose(conn,rs,ps);
}
returnflag;
}
}
测试类 Test
packagecom.softeem.test;
importjava.util.Iterator;
importjava.util.List;
importcom.soffteem.daoimpl.UserDaoImpl;
importcom.softeem.dao.UserDao;
importcom.softeem.dto.User;
publicclassTest{
publicstaticvoidmain(String[]args){
// 添加数据
// Useruser=new User(0,"KK","JJ");
// //接口回调
// UserDaouserdao=new UserDaoImpl();
// booleanb=userdao.addUser(user);
//System.out.println("添加成功:"+b);
// 删除数据
// UserDaou2 = new UserDaoImpl();
// booleanb = u2.delUser(1);
//System.out.println("删除成功:" + b);
// 根据id查询数据
// UserDaou3 = new UserDaoImpl();
// Useruser = u3.getUserById(2);
//System.out.println(user.getUid() + " " + user.getUname() + ""
// +user.getUpass());
// 查询所有数据
// UserDaou4 = new UserDaoImpl();
//List<User> list = u4.listUser();// 对象调用方法查询用集合接受因为有很多条记录
//Iterator<User> user = list.iterator();//对集合的数据进行迭代
// while(user.hasNext()) {
// User u= user.next();// 一个一个的循环对象
//System.out.println(u.getUid() + " " + u.getUname() + " "
// +u.getUpass());
// }[W用6]
// 更新数据
UserDaou5=newUserDaoImpl();
Useruser=newUser(7,"战神七","专业一换一");
booleanb=u5.updateUser(user);
System.out.println("更新数据成功!"+b);
}
}