1月 18

查找方法

    public StudentInfo getStudent(int id) throws SQLException{ 
  StudentInfo studentInfo=null;
   
  String sql = "select id,name, sex, age from  [tb_Student] where id=?";
  PreparedStatement preparedStatement = conn.prepareStatement(sql);
  preparedStatement.setInt(1, id);
  ResultSet resultSet = preparedStatement.executeQuery(); 
  if(resultSet.next()){
   studentInfo=new StudentInfo();
   studentInfo.setId(resultSet.getInt("id"));
   studentInfo.setName(resultSet.getString("name")); 
   studentInfo.setSex(resultSet.getInt("sex"));
   studentInfo.setAge(resultSet.getInt("age")); 
  }  
  return studentInfo;
 }

调用方法

    AddTest addTest=new AddTest(); 
    
      StudentInfo studentInfo=new StudentInfo();
     studentInfo=addTest.getStudent(100);
     System.out.println(studentInfo.toString());

如果是查找多条

把if(resultSet.next()){ 换成 while(resultSet.next()){

加List返回就可以了

written by ocean

1月 18

更新方法

    public void updateStudent(StudentInfo studentInfo) throws SQLException{ 
 String sql = "update  [tb_Student] set name=? ,sex=?,age=? where id=?";
 PreparedStatement preparedStatement = conn.prepareStatement(sql);
 preparedStatement.setString(1, studentInfo.getName());
 preparedStatement.setInt(2, studentInfo.getSex());
 preparedStatement.setInt(3, studentInfo.getAge());
 preparedStatement.setInt(4, studentInfo.getId());
 preparedStatement.executeUpdate(); 
}

调用方法

    AddTest addTest=new AddTest();
     for(int index=120;index<130;index++){ 
      StudentInfo studentInfo=new StudentInfo();
      studentInfo.setId(index);
      studentInfo.setName("更新ysuhy"+index);
      studentInfo.setAge(999);
      studentInfo.setSex(index%2+10);
      addTest.updateStudent(studentInfo); 
     }

结论:

其实和增加,删除完全一样,除了sql

这点类似于.net

written by ocean

1月 18

删除方法

    public void deleteStudent(int id) throws SQLException{ 
  String sql = "delete from  [tb_Student] where id=?";
  PreparedStatement preparedStatement = conn.prepareStatement(sql);
  preparedStatement.setInt(1, id);
  preparedStatement.executeUpdate(); 
 }

调用

    AddTest addTest=new AddTest();
for(int index=150;index<160;index++){     
 addTest.deleteStudent(index);
}

结论:

其实和增加完全一样,除了sql

written by ocean

1月 18

添加方法

    public class AddTest {
 
   
 public AddTest(){
   
  this.conn=getConnValue();
 } 
 private Connection conn;
 
 public Connection getConnValue() {
  try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=DB_JavaTest";
   String username = "sa";
   String password = "*****";
   conn = DriverManager.getConnection(url, username, password);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
 } 
  
 public void addStudent(StudentInfo studentInfo) throws SQLException{ 
  String sql = "insert into [tb_Student] (name, sex, age) values(?,?,?)";
  PreparedStatement preparedStatement = conn.prepareStatement(sql);
  preparedStatement.setString(1, studentInfo.getName());
  preparedStatement.setInt(2, studentInfo.getSex());
  preparedStatement.setInt(3, studentInfo.getAge());
  preparedStatement.executeUpdate(); 
 } 
}

调用

    AddTest addTest=new AddTest();
   for(int index=100;index<200;index++){ 
    StudentInfo studentInfo=new StudentInfo();
    studentInfo.setName("ysuhy"+index);
    studentInfo.setAge(26+index/3);
    studentInfo.setSex(index%2);
    addTest.addStudent(studentInfo); 
   }

written by ocean

1月 18

学习一下JDBC,第一步

连接数据库

不是太顺利,网上找了几个教程,发现我自己用起来都会有问题,也不知道是我自己的人品有问题还是这些人压根就没测试

先上代码

  public static void main(String[] args) throws SQLException {
  try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   System.out.println("数据库驱动程序注册成功!");
   String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=DB_JavaTest";
   String user = "sa";
   String password = "我是密码";
   Connection conn = DriverManager.getConnection(url, user, password);
   System.out.println("数据库连接成功");
  } catch (Exception e) {
   e.printStackTrace();
   System.out.println("数据库连接失败");
  }
 }

 其中Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");是用来预加载jar包到内存中

可以理解为预热

DriverManager  根据url实例出对应的connection实例

 

在SQL Server 2000 中加载驱动和URL路径的语句是

String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String dbURL = "jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sample";

而SQL Server 2005 中加载驱动和URL的语句则为

String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=sample";

 

最后说个很奇怪的问题

我用localhost连接一直出错,后来改成127.0.0.1OK了

 

written by ocean