JAVA OOP第八章JDBC 上机+课后

上机1  建立连接数据库package cn.happy4.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class test {public static void main(String[] args) throws Exception{// TODO Auto-generated method stubConnection con=null;try{ String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";  String url="jdbc:sqlserver://localhost:1433;DatabaseName=MySchool";  String username="sa";  String pwd="1";  Class.forName(driver); con=DriverManager.getConnection(url, username, pwd);System.out.println("建立连接成功！");}catch(SQLException e){System.out.println("建立连接失败");}finally{try{if(null!=con){con.close();System.out.println("关闭连接成功");}}catch(SQLException e){System.out.println("关闭连接失败");}}}}

上机2  查询信息package cn.happy5.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class TestJDBC2 {public static void main(String[] args) throws Exception{// TODO Auto-generated method stubResultSet rs=null; String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";  String url="jdbc:sqlserver://localhost:1433;DatabaseName=epet";  String username="sa";  String pwd="1";  Class.forName(driver);  Connection con=DriverManager.getConnection(url, username, pwd);  Statement state=con.createStatement();  rs=state.executeQuery("select * from Person");  System.out.println("主人信息列表：");  System.out.println("编号 \t姓名\t元宝数");  while(rs.next()){  System.out.print(rs.getInt("id")+"\t");  System.out.print(rs.getString("name")+"\t");  System.out.println(rs.getInt("money")+"\t");  }  state.close();  con.close();  }}

上机3 登录package cn.happy6.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Scanner;public class Test {public static void main(String[] args) throws Exception{// TODO Auto-generated method stubScanner input=new Scanner(System.in);Connection con=null;Statement stmt=null;ResultSet rs=null;System.out.println("\t宠物主人登录");System.out.println("请输入姓名：");String name=input.next();System.out.println("请输入密码：");String pwds=input.next();try{ String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";  String url="jdbc:sqlserver://localhost:1433;DatabaseName=epet";  String username="sa";  String pwd="1";  Class.forName(driver); con=DriverManager.getConnection(url, username, pwd);  stmt=con.createStatement();  String sql="select * from person where name='"+name+"' and pwd='"+pwds+"'";  System.out.println(sql);  rs=stmt.executeQuery(sql);  if(rs.next()){  System.out.println("登录成功");  } else{  System.out.println("登录失败");  }}catch(SQLException e){System.out.println("有异常");}finally{ stmt.close();  con.close();}  }}

上机4 使用preartstatement修改信息package cn.happy6.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner;public class Test1 {public static void main(String[] args) throws Exception{// TODO Auto-generated method stubScanner input=new Scanner(System.in);Connection con=null;PreparedStatement stmt=null;ResultSet rs=null;try{ String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";  String url="jdbc:sqlserver://localhost:1433;DatabaseName=epet";  String username="sa";  String pwd="1";  Class.forName(driver); con=DriverManager.getConnection(url, username, pwd); String sql="update Dog set health=?,love=?  where id=?";  stmt=con.prepareStatement(sql);  stmt.setInt(1, 80);  stmt.setInt(2, 11);  stmt.setInt(3, 1);  stmt.executeUpdate();  stmt.setInt(1, 91);  stmt.setInt(2, 10);  stmt.setInt(3, 2);  stmt.executeUpdate();  System.out.println("更新狗狗成功");}catch(SQLException e){System.out.println("更新狗狗失败");}finally{ stmt.close();  con.close();}}}

使用prepareStatement 添加信息package cn.happy7.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;public class TestJDBC3 {public static void main(String[] args) throws Exception {// TODO Auto-generated method stub//Scanner input = new Scanner(System.in);Connection con = null;PreparedStatement stmt = null;//ResultSet rs = null;try {String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";String url = "jdbc:sqlserver://localhost:1433;DatabaseName=epet";String username = "sa";String pwd = "1";Class.forName(driver);con = DriverManager.getConnection(url, username, pwd);String sql = "insert into Dog values (?,?,?,?)";stmt = con.prepareStatement(sql);stmt.setString(1, "妹妹");stmt.setInt(2, 100);stmt.setInt(3, 0);stmt.setString(4, "泰迪");stmt.execute();stmt.setString(1, "啦啦");stmt.setInt(2, 10);stmt.setInt(3, 0);stmt.setString(4, "阿拉斯加");stmt.execute();System.out.println("添加狗狗成功");}catch (SQLException e) {e.printStackTrace();System.out.println("添加狗狗失败");} finally {stmt.close();con.close();}}}

--------------------------------------------------------------课后package cn.happy8.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Student {public static void main(String[] args) throws Exception {Connection con = null;PreparedStatement ps=null;ResultSet rs = null;Statement st=null;try {String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";String url = "jdbc:sqlserver://localhost:1433;DatabaseName=epet";String username = "sa";String pwd = "1";Class.forName(driver);con = DriverManager.getConnection(url, username, pwd);String sql = "insert into Student values(?,?)";ps=con.prepareStatement(sql);ps.setInt(1,8);ps.setString(2, "恩恩");int count=ps.executeUpdate();if (count > 0) {System.out.println("添加成功！！");} else {System.out.println("添加失败！！");}String sql1 = "select count(*) as a from student";st=con.createStatement();rs = st.executeQuery(sql1);if (rs != null) {while (rs.next()) {int c = rs.getInt("a");System.out.println("表中记录数：" + c);}}} catch (SQLException e) {e.printStackTrace();} finally {con.close();}}}

package cn.happy8.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Test {public static void main(String[] args) throws Exception {Connection con = null;Statement state = null;ResultSet rs = null;try {String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";String url = "jdbc:sqlserver://localhost:1433;DatabaseName=epet";String username = "sa";String pwd = "1";Class.forName(driver);con = DriverManager.getConnection(url, username, pwd);  state=con.createStatement();  rs=state.executeQuery("select * from Student");  System.out.println("主人信息列表：");  System.out.println("id\t姓名");  while(rs.next()){  System.out.print(rs.getInt("id")+"\t");  System.out.print(rs.getString("name")+"\t"); System.out.println();  }}finally{  state.close();  con.close();    }}}

package cn.happy8.com;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner;public class Test1 {public static void main(String[] args) throws Exception{// TODO Auto-generated method stubScanner input=new Scanner(System.in);Connection conn = null;PreparedStatement stmt=null;ResultSet rs=null; String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";  String url="jdbc:sqlserver://localhost:1433;DatabaseName=epet";  String username="sa";  String pwd="1";    Class.forName(driver);  conn=DriverManager.getConnection(url, username, pwd);  //Statement state=conn.createStatement();  try{   System.out.println("\t宠物主人登录");  System.out.println("请输入姓名：");  String name=input.next();  System.out.println("请输入密码：");  String pwds=input.next();  String sql="select * from person where name=? and pwd=?";  stmt=conn.prepareStatement(sql);  stmt.setString(1, name);  stmt.setString(2, pwds);   rs = stmt.executeQuery();  if(rs!=null){  if(rs.next()){  System.out.println("登录成功");  }else{  System.out.println("失败");  }  }  }catch(SQLException e){e.printStackTrace();}finally{ stmt.close();  conn.close();}}}