java jdbc连接和使用详细介绍
程序员文章站
2024-03-09 17:57:23
java jdbc连接和使用
jdbc
导入驱动
//jar是已经打包好的class文件集,可以引用到其他工程中
//build path中add e...
java jdbc连接和使用
jdbc
导入驱动
//jar是已经打包好的class文件集,可以引用到其他工程中
//build path中add external jars导入
连接jdbc
1. 加载驱动
class.from("com.mysql.jdbc.driver");
创建连接
//导包使用 java.sql.*; string jdbc="jdbc:mysql://localhost:3306/student?user=root&password=&characterencoding=utf-8";//student是表名 connection conn = drivermanager.getconnection(jdbc);
2. 注意 数据库打开之后一定要记得关。
conn.close();
1. 执行sql语句 (创建表,插入,删除,更新)
使用statemant
statemant st = conn.createstatemant(); int row = st.executeupdate(sql语句);//不能做查询操作。
使用preparestatement
可以使用?占位符来代替你需要传递的参数
string sql = "insert into " + tablename + "(name,subject,score) values(?,?,?)"; preparestatement pt = conn.preparestatement(sql); //给每一位占位符设置值,下标从1开始 pt.setstring(1,score.getname()); pt.setstring(2.score.getsubject()); pt.setdouble(3,score.getscore()); //使用无参的方法 pt.executeupdate();
1.查询操作
static list<score> queryscore(connection pconn, score pscore) throws sqlexception { arraylist<score> mlist = new arraylist<>(); string sql = "select * from " + tablename + " where name = ?"; preparedstatement ps = pconn.preparestatement(sql); ps.setstring(1, pscore.getname()); resultset rs = ps.executequery(); while (rs.next()) { // 这里可以通过rs获取所有结果 string subject = rs.getstring("subject"); int id = rs.getint("id"); double score = rs.getdouble("score"); mlist.add(new score(id, pscore.getname(), subject, score)); } return mlist; }
下面是一个小程序
//建立数据库连接类 public class dao { // 放问数据库的链接地址 static string jdbc = "jdbc:mysql://localhost:3306/student?user=root&password=&characterencoding=utf-8"; // 打开链接 public static connection connection() { // 使用jdbc的步骤 // 1. 加载jdbc驱动 try { // 类的全名 包名+类名 class.forname("com.mysql.jdbc.driver"); // 2. 连接数据库 connection conn = drivermanager.getconnection(jdbc); return conn; } catch (exception e) { system.out.println("驱动加载失败"); return null; } } } //分数类 public class score { string name; string id; string subject; double score; public score(string name, string subject, double score) { super(); this.name = name; this.subject = subject; this.score = score; } @override public string tostring() { return "score [name=" + name + ", id=" + id + ", subject=" + subject + ", score=" + score + "]"; } public score(string name, string id, string subject, double score) { super(); this.name = name; this.id = id; this.subject = subject; this.score = score; } public string getname() { return name; } public void setname(string name) { this.name = name; } public string getid() { return id; } public void setid(string id) { this.id = id; } public string getsubject() { return subject; } public void setsubject(string subject) { this.subject = subject; } public double getscore() { return score; } public void setscore(double score) { this.score = score; } } //实现类 public class test { public static string tablename = "score"; public static void main(string[] args) { try { connection conn = dao.connection(); if (conn != null) { system.out.println("链接上了"); // createtable(conn); // 插入一条记录 // score score = new score("李四 ", "android", 98); // system.out.println(addscore2(conn, score)); // deletescore(conn, score); // updatescore(conn, score); list<score> list = queryscorebyname(conn, "王五"); //queryallscore(conn); for (score score : list) { system.out.println(score); } conn.close(); } else { system.out.println("链接失败 "); } } catch (sqlexception e) { e.printstacktrace(); } } // 创建一张表 public static boolean createtable(connection conn) { // 开始执行sql语句 string sql = "create table " + tablename + "(id integer primary key auto_increment,name varchar(3) not null,subject varchar(20) not null,score double)"; // 要执行一条语句,需要一个执行的类 statement try { statement st = conn.createstatement(); int result = st.executeupdate(sql); system.out.println(result); if (result != -1) return true; } catch (sqlexception e) { e.printstacktrace(); } return false; } // 添加一条记录 public static boolean addscore(connection conn, score score) throws sqlexception { string sql = "insert into " + tablename + "(name,subject,score) values('" + score.getname() + "','" + score.getsubject() + "'," + score.getscore() + ")"; system.out.println(sql); statement st = conn.createstatement(); int row = st.executeupdate(sql); if (row > 0) return true; return false; } // 添加一条记录2 public static boolean addscore2(connection conn, score score) throws sqlexception { // 占位符?来代替需要设置的参数 string sql = "insert into " + tablename + "(name,subject,score) values(?,?,?)"; preparedstatement ps = conn.preparestatement(sql); // 必须给定?所代表的值 ps.setstring(1, score.getname()); ps.setstring(2, score.getsubject()); ps.setdouble(3, score.getscore()); // 调用无参的方法 int row = ps.executeupdate(); if (row > 0) return true; return false; } public static boolean deletescore(connection conn, score score) throws sqlexception { string sql = "delete from " + tablename + " where name=? and subject=?"; // 创建preparestatement preparedstatement ps = conn.preparestatement(sql); ps.setstring(1, score.getname()); ps.setstring(2, score.getsubject()); // ps.setdouble(3, score.getscore()); // 执行 int row = ps.executeupdate(); system.out.println(row); if (row > 0) return true; return false; } public static boolean updatescore(connection conn, score score) throws sqlexception { // 修改 score人他的科目的成绩 string sql = "update " + tablename + " set score=? where name=? and subject=?"; preparedstatement ps = conn.preparestatement(sql); ps.setdouble(1, score.getscore()); ps.setstring(2, score.getname()); ps.setstring(3, score.getsubject()); int row = ps.executeupdate(); system.out.println(row); if (row > 0) return true; return false; } public static list<score> queryallscore(connection conn) throws sqlexception { string sql = "select * from " + tablename; // 开始查询 statement st = conn.createstatement(); resultset rs = st.executequery(sql); list<score> list = new arraylist<score>(); while (rs.next()) { // 这里可以通过rs获取所有结果 string id = rs.getstring("id"); string name = rs.getstring("name"); string subject = rs.getstring("subject"); double score = rs.getdouble("score"); list.add(new score(name, id, subject, score)); } // 结束 return list; } public static list<score> queryscorebyname(connection conn, string name) throws sqlexception { string sql = "select * from " + tablename + " where name=?"; preparedstatement pt = conn.preparestatement(sql); pt.setstring(1, name); resultset rs = pt.executequery(); list<score> list = new arraylist<>(); while (rs.next()) { string subject = rs.getstring("subject"); string id = rs.getstring("id"); double score = rs.getdouble("score"); list.add(new score(name, id, subject, score)); } return list; }
下一篇: java 线程锁详细介绍及实例代码