JAVA数据库编程基础+源代码

/**
* @author 东海 陈光剑 chenguangjian 2013-4-9上午10:42:40
* Email: universsky@126.com
* Blog: http://blog.sina.com.cn/universsky11
* http://blog.csdn.net/universsky
*/
package isword;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class dduserDBConnection {
/** * 获取数据连接
驱动：oracle.jdbc.driver.OracleDriverURL：jdbc:oracle:thin:@<machine_name><:port>:dbname
jdbc:oracle:thin:@localhost:1521:orcl
注：machine_name：数据库所在的机器的名称ip；
port：端口号，默认是1521
username:默认是scott
password:默认是tiger
* @throws SQLException
* */
public static void main(String[]args) throws SQLException{

String subscribercode="826610014";
String qry1="select * from CBE_SUBSCRIBER where subscribercode=";

String qry=qry1+subscribercode;

dduserDBConnection dc=new dduserDBConnection();
Connection conn=null;

String ip="10.137.5.61";//db ip
String port="1521";//
String sid="ora61";
String userName="dduserdb";
String passWord="IVcbs33";


conn=dc.getConnection(ip, port, sid, userName, passWord);
System.out.println("dc.getConnection="+conn);


// 从下面开始，和SQL Server一模一样
Statement sm = conn.createStatement();
//ResultSet rs = sm.executeQuery("select * from cbe_policy where ATTACHPRICEPLANID=10");

ResultSet rs = sm.executeQuery(qry);
System.out.println("sm.executeQuery="+rs);
/**
* public interface DatabaseMetaData extends Wrapper
* 关于数据库的整体综合信息。

*/
DatabaseMetaData dbmd=conn.getMetaData();
/**
Retrieves a DatabaseMetaData object that contains metadata about the database to which this Connection object represents a connection. The metadata includes information about the database's tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on.
*/
System.out.println("conn.getMetaData()="+dbmd);
System.out.println("dbmd.getConnection()="+dbmd.getConnection());//获取此元数据对象所产生的连接。
System.out.println("getCatalogSeparator()="+dbmd.getCatalogSeparator());
System.out.println("getCatalogTerm()="+dbmd.getCatalogTerm());
System.out.println("getCatalogTerm()="+dbmd.getCatalogTerm());
System.out.println("getDatabaseMajorVersion()="+dbmd.getDatabaseMajorVersion());
System.out.println("getDriverName()="+dbmd.getDriverName());
System.out.println("getDriverVersion()="+dbmd.getDriverVersion());
System.out.println("getMaxColumnsInIndex()="+dbmd.getMaxColumnsInIndex());
//System.out.println(dbmd.);

ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();//返回此 ResultSet 对象中的列数。

System.out.println("numberOfColumns="+numberOfColumns);


//for(int i=1;i<numberOfColumns;i++)
for(int i=1;i<numberOfColumns;i++)
{
System.out.print("\t "+rsmd.getColumnName(i));
}


System.out.print("\n");



//for (int columnIndex=1;columnIndex<numberOfColumns;columnIndex++)
/*for (int columnIndex=1;columnIndex<numberOfColumns;columnIndex++)
{ //用户名
rs.next();//When a call to the next method returns false, the cursor is positioned after the last row.
System.out.print("\t "+rs.getString(columnIndex)+" ");
默认是从1开始编号的 , 超出列序号范围,会报错:无效的列索引范围

/**String getNString(int columnIndex)
* 以 Java 编程语言中 String 的形式获取此 ResultSet 对象的当前行中指定列的值。
*/


//}*/
String subscriberkey=null;

while(rs.next()){

System.out.print("\t "+rs.getString(1)+" ");
subscriberkey=rs.getString(1);
System.out.print("\t "+rs.getString(2)+" ");
System.out.print("\t "+rs.getString(3)+" ");
System.out.print("\t "+rs.getString(4)+" ");
System.out.print("\t "+rs.getString(5)+" ");
System.out.print("\t "+rs.getString(6)+" ");
System.out.print("\t "+rs.getString(7)+" ");
System.out.print("\t "+rs.getString(8)+" ");
System.out.print("\t "+rs.getString(9)+" ");


}

System.out.print("\n\nsubscriberkey="+subscriberkey);


}



//**************************************************************************************//


public Connection getConnection(String ip,String port,String sid,String userName,String passWord )
{
String driverName="oracle.jdbc.driver.OracleDriver";// 连接oracle驱动包
String dbUrl="jdbc:oracle:thin:@"+ip+":"+port+":"+sid+"";
Connection conn=null;

try {
Class.forName(driverName);
conn = DriverManager.getConnection(dbUrl, userName, passWord);

} catch (ClassNotFoundException e)
{
// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();

}
//Connection conn;
return conn;
}
}

/**
* @author 东海 陈光剑 chenguangjian 2013-4-9上午10:42:40
* Email: universsky@126.com
* Blog: http://blog.sina.com.cn/universsky11
* http://blog.csdn.net/universsky
*/
package isword;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class ddsysDBConnection {
/** * 获取数据连接
驱动：oracle.jdbc.driver.OracleDriverURL：jdbc:oracle:thin:@<machine_name><:port>:dbname
jdbc:oracle:thin:@localhost:1521:orcl
注：machine_name：数据库所在的机器的名称ip；
port：端口号，默认是1521
username:默认是scott
password:默认是tiger
* @throws SQLException
* */
public static void main(String[]args) throws SQLException{

//String subscribercode="826610014";
//String qry1="select * from CBE_SUBSCRIBER where subscribercode=";

//String qry=qry1+subscribercode;
String table_name="ts_priceplan";
String qry1="select * from ";
String qry=qry1+table_name;

ddsysDBConnection dc=new ddsysDBConnection();
Connection conn=null;

String ip="10.137.5.61";//db ip
String port="1521";//
String sid="ora61";
String userName="ddsysdb";
String passWord="IVcbs33";


conn=dc.getConnection(ip, port, sid, userName, passWord);
System.out.println("dc.getConnection="+conn);


// 从下面开始，和SQL Server一模一样
Statement sm = conn.createStatement();
//ResultSet rs = sm.executeQuery("select * from cbe_policy where ATTACHPRICEPLANID=10");

ResultSet rs = sm.executeQuery(qry);
System.out.println("sm.executeQuery="+rs);
/**
* public interface DatabaseMetaData extends Wrapper
* 关于数据库的整体综合信息。

*/
DatabaseMetaData dbmd=conn.getMetaData();
/**
Retrieves a DatabaseMetaData object that contains metadata about the database to which this Connection object represents a connection. The metadata includes information about the database's tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on.
*/
System.out.println("conn.getMetaData()="+dbmd);
System.out.println("dbmd.getConnection()="+dbmd.getConnection());//获取此元数据对象所产生的连接。
System.out.println("getCatalogSeparator()="+dbmd.getCatalogSeparator());
System.out.println("getCatalogTerm()="+dbmd.getCatalogTerm());
System.out.println("getCatalogTerm()="+dbmd.getCatalogTerm());
System.out.println("getDatabaseMajorVersion()="+dbmd.getDatabaseMajorVersion());
System.out.println("getDriverName()="+dbmd.getDriverName());
System.out.println("getDriverVersion()="+dbmd.getDriverVersion());
System.out.println("getMaxColumnsInIndex()="+dbmd.getMaxColumnsInIndex());
//System.out.println(dbmd.);

ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();//返回此 ResultSet 对象中的列数。

System.out.println("numberOfColumns="+numberOfColumns);

for(int i=1;i<numberOfColumns;i++){
System.out.print("\t "+rsmd.getColumnName(i));
}

System.out.print("\n");
String issue_status=null;
//for (int columnIndex=1;columnIndex<numberOfColumns;columnIndex++)
for (int columnIndex=1;columnIndex<numberOfColumns ;columnIndex++)
{ //用户名

//System.out.print(rs.next());
rs.next();
issue_status=rs.getString(4);
System.out.print("\t "+rs.getString(columnIndex)+" ");
默认是从1开始编号的 , 超出列序号范围,会报错:无效的列索引范围

/**String getNString(int columnIndex)
* 以 Java 编程语言中 String 的形式获取此 ResultSet 对象的当前行中指定列的值。
*/

}
System.out.print("\n issue_status= "+issue_status);



}



public Connection getConnection(String ip,String port,String sid,String userName,String passWord )
{
String driverName="oracle.jdbc.driver.OracleDriver";// 连接oracle驱动包
String dbUrl="jdbc:oracle:thin:@"+ip+":"+port+":"+sid+"";
Connection conn=null;

try {
Class.forName(driverName);
conn = DriverManager.getConnection(dbUrl, userName, passWord);

} catch (ClassNotFoundException e)
{
// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();

}
//Connection conn;
return conn;
}
}