java jdbc连接和使用详细介绍
javajdbc连接和使用
jdbc
导入驱动
//jar是已经打包好的class文件集，可以引用到其他工程中
//BuildPath中addexternaljars导入
连接JDBC
1.加载驱动
Class.from("com.mysql.jdbc.Driver");
创建连接
//导包使用java.sql.*； Stringjdbc="jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8";//student是表名 Connectionconn=DriverManager.getConnection(jdbc);
2.注意数据库打开之后一定要记得关。
conn.close();
1.执行SQL语句（创建表，插入，删除，更新）
使用Statemant
Statemantst=conn.createStatemant(); introw=st.executeUpdate(sql语句);//不能做查询操作。
使用PrepareStatement
可以使用？占位符来代替你需要传递的参数
Stringsql="insertinto"+TABLENAME +"(name,subject,score)values(?,?,?)"; PrepareStatementpt=conn.prepareStatement(sql); //给每一位占位符设置值,下标从1开始 pt.setString(1,score.getName()); pt.setString(2.score.getSubject()); pt.setDouble(3,score.getScore()); //使用无参的方法 pt.executeUpdate();
1.查询操作
staticList<Score>queryScore(Connectionpconn,ScorepScore)
throwsSQLException{
ArrayList<Score>mlist=newArrayList<>();
Stringsql="select*from"+TABLENAME+"wherename=?";
PreparedStatementps=pconn.prepareStatement(sql);
ps.setString(1,pScore.getName());
ResultSetrs=ps.executeQuery();
while(rs.next()){
//这里可以通过rs获取所有结果
Stringsubject=rs.getString("subject");
intid=rs.getInt("id");
doublescore=rs.getDouble("score");
mlist.add(newScore(id,pScore.getName(),subject,score));
}
returnmlist;
}
下面是一个小程序
//建立数据库连接类
publicclassDAO{
//放问数据库的链接地址
staticStringjdbc="jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8";
//打开链接
publicstaticConnectionconnection(){
//使用JDBC的步骤
//1.加载JDBC驱动
try{
//类的全名包名+类名
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库
Connectionconn=DriverManager.getConnection(jdbc);
returnconn;
}catch(Exceptione){
System.out.println("驱动加载失败");
returnnull;
}
}
}
//分数类
publicclassScore{
Stringname;
Stringid;
Stringsubject;
doublescore;
publicScore(Stringname,Stringsubject,doublescore){
super();
this.name=name;
this.subject=subject;
this.score=score;
}
@Override
publicStringtoString(){
return"Score[name="+name+",id="+id+",subject="+subject
+",score="+score+"]";
}
publicScore(Stringname,Stringid,Stringsubject,doublescore){
super();
this.name=name;
this.id=id;
this.subject=subject;
this.score=score;
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
publicStringgetId(){
returnid;
}
publicvoidsetId(Stringid){
this.id=id;
}
publicStringgetSubject(){
returnsubject;
}
publicvoidsetSubject(Stringsubject){
this.subject=subject;
}
publicdoublegetScore(){
returnscore;
}
publicvoidsetScore(doublescore){
this.score=score;
}
}
//实现类
publicclassTest{
publicstaticStringTABLENAME="score";
publicstaticvoidmain(String[]args){
try{
Connectionconn=DAO.connection();
if(conn!=null){
System.out.println("链接上了");
//createTable(conn);
//插入一条记录
//Scorescore=newScore("李四","Android",98);
//System.out.println(addScore2(conn,score));
//deleteScore(conn,score);
//updateScore(conn,score);
List<Score>list=queryScoreByName(conn,"王五");//queryAllScore(conn);
for(Scorescore:list){
System.out.println(score);
}
conn.close();
}else{
System.out.println("链接失败");
}
}catch(SQLExceptione){
e.printStackTrace();
}
}
//创建一张表
publicstaticbooleancreateTable(Connectionconn){
//开始执行sql语句
Stringsql="createtable"
+TABLENAME
+"(idintegerprimarykeyauto_increment,namevarchar(3)notnull,subjectvarchar(20)notnull,scoredouble)";
//要执行一条语句，需要一个执行的类Statement
try{
Statementst=conn.createStatement();
intresult=st.executeUpdate(sql);
System.out.println(result);
if(result!=-1)
returntrue;
}catch(SQLExceptione){
e.printStackTrace();
}
returnfalse;
}
//添加一条记录
publicstaticbooleanaddScore(Connectionconn,Scorescore)
throwsSQLException{
Stringsql="insertinto"+TABLENAME
+"(name,subject,score)values('"+score.getName()+"','"
+score.getSubject()+"',"+score.getScore()+")";
System.out.println(sql);
Statementst=conn.createStatement();
introw=st.executeUpdate(sql);
if(row>0)
returntrue;
returnfalse;
}
//添加一条记录2
publicstaticbooleanaddScore2(Connectionconn,Scorescore)
throwsSQLException{
//占位符?来代替需要设置的参数
Stringsql="insertinto"+TABLENAME
+"(name,subject,score)values(?,?,?)";
PreparedStatementps=conn.prepareStatement(sql);
//必须给定？所代表的值
ps.setString(1,score.getName());
ps.setString(2,score.getSubject());
ps.setDouble(3,score.getScore());
//调用无参的方法
introw=ps.executeUpdate();
if(row>0)
returntrue;
returnfalse;
}
publicstaticbooleandeleteScore(Connectionconn,Scorescore)
throwsSQLException{
Stringsql="deletefrom"+TABLENAME+"wherename=?andsubject=?";
//创建PrepareStatement
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,score.getName());
ps.setString(2,score.getSubject());
//ps.setDouble(3,score.getScore());
//执行
introw=ps.executeUpdate();
System.out.println(row);
if(row>0)
returntrue;
returnfalse;
}
publicstaticbooleanupdateScore(Connectionconn,Scorescore)
throwsSQLException{
//修改score人他的科目的成绩
Stringsql="update"+TABLENAME
+"setscore=?wherename=?andsubject=?";
PreparedStatementps=conn.prepareStatement(sql);
ps.setDouble(1,score.getScore());
ps.setString(2,score.getName());
ps.setString(3,score.getSubject());
introw=ps.executeUpdate();
System.out.println(row);
if(row>0)
returntrue;
returnfalse;
}
publicstaticList<Score>queryAllScore(Connectionconn)
throwsSQLException{
Stringsql="select*from"+TABLENAME;
//开始查询
Statementst=conn.createStatement();
ResultSetrs=st.executeQuery(sql);
List<Score>list=newArrayList<Score>();
while(rs.next()){
//这里可以通过rs获取所有结果
Stringid=rs.getString("id");
Stringname=rs.getString("name");
Stringsubject=rs.getString("subject");
doublescore=rs.getDouble("score");
list.add(newScore(name,id,subject,score));
}
//结束
returnlist;
}
publicstaticList<Score>queryScoreByName(Connectionconn,Stringname)
throwsSQLException{
Stringsql="select*from"+TABLENAME+"wherename=?";
PreparedStatementpt=conn.prepareStatement(sql);
pt.setString(1,name);
ResultSetrs=pt.executeQuery();
List<Score>list=newArrayList<>();
while(rs.next()){
Stringsubject=rs.getString("subject");
Stringid=rs.getString("id");
doublescore=rs.getDouble("score");
list.add(newScore(name,id,subject,score));
}
returnlist;
}