J2EE学习笔记--JDBC

一、mysql的启动与创建表格：  

  create database msdata;  //创建mydata数据库   

  use mydata;  

  create table dept  (     deptno int primary key,     dname varchar(14),     loc varchar(13)     ); 

Note：在命令行中逐行输入时，容易出错，可以编写小的.sql脚本。在命令行中，可以用/.来执行一个docBase

(绝对路径)//.sql脚本。  

show databases;// 显示数据库； 

show tables;//显示数据库mydata中的表； 

desc dept；//查看dept表的结构；  

insert  into  dept values(10，abc，abc);//向数据库中插入数据 ;

commit；//提交  

二、数据库之间的区别： 

1、数据类型不同；  

2、分页程序；    

select *    

 from dept    

order by deptno desc //按deptno的降序排列；    

limit 3,2;//从第三条之后数两条；  

3、数据库的递增字段；  

create table article   (    id int primary key auto_increment,     title varchar(255)   );  

insert into article values(null,'123');  

insert into article values(null,'456');  

 数据库会自动插入（1，123）和（2，456）。  

或者用： insert into article(title) values('你好啊！')。     

日期处理：    select now();   select date_format(now(),'%Y %D %M %h:%i:%s %x');

 三、jdbc编程步骤： 

以mysql为例：

import java.sql.*;

public class TestJDBC {

   public static void main(String[] args) {   

      Connection conn = null;  

      Statement stmt = null;  

      ResultSet rs = null; 

      try {   

        Class.forName("com.mysql.jdbc.Driver");//Load the Driver     

        conn =          DriverManager.getConnection("jdbc:mysql://localhost/mydata?" +                                      "user=root&password=19871114");//Connect to the DataBase    

       // Execute the SQL     

      stmt = conn.createStatement();     

      rs = stmt.executeQuery("select * from student");     

        while(rs.next()){  //Retrive the result data     

                  //Show the result data       

                 System.out.print(rs.getString("Sno"));     

                 System.out.println(rs.getString("Sname"));     

         }  

     }catch (ClassNotFoundException e) {   

              e.printStackTrace();  

     }catch (SQLException ex) {     

              System.out.println("SQLException: " + ex.getMessage());     

              System.out.println("SQLState: " + ex.getSQLState());     

              System.out.println("VendorError: " + ex.getErrorCode());  

      }finally{    //close   

                 try{   

                     if(rs != null){     

                          conn.close();     

                          conn = null;   

                      }  

                    if(stmt != null){     

                          stmt.close();     

                          stmt = null;   

                      }    

                      if(conn != null){    

                           conn.close();     

                           conn = null;        

                       }  

                   }catch(SQLException ex2){   

                           ex2.printStackTrace();   

                   }  

              }

          }

}