Опытный
Профиль
Группа: Участник
Сообщений: 302
Регистрация: 5.11.2007
Репутация: нет
Всего: 1
|
Решил запрос создал таблицу table2 с поле xml_file(text) создал функцию в постгресе | Код |
CREATE OR REPLACE FUNCTION "public"."function1" () RETURNS text AS
$body$
DECLARE
BEGIN
INSERT INTO
public.table2(
xml_file)
select query_to_xml('select * from table',true,false,'');
return null;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
|
в яве беру данные из базы модель таблицы | Код |
package library;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Vector;
import javax.swing.table.AbstractTableModel;
/*
* Created on 14.06.2004
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
/**
* @author golden
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class database2 extends AbstractTableModel{
/**
*
*/
private static final long serialVersionUID = 1L;
public String[] columnNames;
public Vector rows = new Vector();
public ResultSetMetaData metaData;
public Statement statement;
public ResultSet resultSet;
public Connection con=null;
public String tableName;
public int getColumnCount()
{
return columnNames.length;
}
public String getColumnName(int n)
{
return columnNames[n];
}
public int getRowCount()
{
return rows.size();
}
public int addRow()
{
int r=rows.size()+1;
return r;
}
public Object getValueAt(int aRow, int aColumn)
{
Vector row = (Vector)rows.elementAt(aRow);
return row.elementAt(aColumn);
}
//---------------------------------------------------------------
public database2(String query, String DBNAME, String USERNAME, String PASSWORD, String tableName, String tableName1, String tableName2,String[] columnNames)
{
this.tableName = tableName;
fireTableDataChanged();
Driver d;
try{
d = (Driver)Class.forName("org.postgresql.Driver").newInstance();
}
catch (Exception e) {
System.out.println("Error 1");
System.out.println(e.getStackTrace());
}
try{
String ip="";
Ip Ip = new Ip();
ip=Ip.ip;
// System.out.println(ip);
if(con==null){
con = DriverManager.getConnection ("jdbc:postgresql://"+ip+":5432/"+DBNAME,USERNAME,PASSWORD);
statement = con.createStatement();
// con.setAutoCommit(true);
}
SqlReload(query);
// con.commit();
// con.close();
}
catch(SQLException e)
{
try {
con.close();
statement.close();
} catch (SQLException e1) {
// TODO Автоматически созданный блок catch
e1.printStackTrace();
}
System.out.println(e.getMessage());
}
}
//---------------------------------------------------------------------------------------------
public boolean isCellEditable(int row, int column)
{
try
{
return metaData.isWritable(column+1);
}
catch (SQLException e)
{
return false;
}
}
//--------------------------------------------------------------------------------------------
public void setValueAt(Object value, int row, int column)
{
try
{
String columnName = getColumnName(column);
String query =
"UPDATE "+tableName+
" SET "+columnName+" = "+
dbRepresentation(column, value)+
" WHERE ";
for(int col = 0; col < getColumnCount();col++)
{
String colName = getColumnName(col);
if (colName==columnName)
{
continue;
}
if (col != 0)
{
query = query + " and ";
}
query = query + colName +" = "+
dbRepresentation(col, getValueAt(row, col));
}
PreparedStatement pstmt =
con.prepareStatement(query);
pstmt.executeUpdate();
if(pstmt == null){
con.rollback();
con.close();
return;
}
Object val;
val = tbRepresentation(column,value);
Vector dataRow = (Vector)rows.elementAt(row);
dataRow.setElementAt(val, column);
fireTableDataChanged();
pstmt.close();
con.commit();
con.close();
// statement.close();
}
catch (Exception ex)
{
try {
con.close();
statement.close();
} catch (SQLException e1) {
// TODO Автоматически созданный блок catch
e1.printStackTrace();
}
System.err.println(ex);
}
}
//-------------------------------------------------------------------------------------------
public Object dbRepresentation(int column, Object value) throws NumberFormatException
{
Object val;
int type;
if (value == null)
{
return "null";
}
try
{ type = metaData.getColumnType(column+1);
}
catch (SQLException e)
{
return value.toString();
}
switch(type)
{
case Types.BIGINT:
return val = new Long(value.toString().trim());
case Types.CHAR:
return val = "'"+new String(value.toString().trim())+"'";
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
return val = new Integer(value.toString().trim());
case Types.REAL:
case Types.FLOAT:
case Types.DOUBLE:
case Types.NUMERIC:
return val = new Double(value.toString().trim());
default: return val = "'"+new String(value.toString().trim())+"'";
}
}
//---------------------------------------------------------------------------------------
public Object tbRepresentation(int column, Object value) throws NumberFormatException
{
Object val;
int type;
if (value == null)
{ return "null"; }
try
{ type = metaData.getColumnType(column+1); }
catch (SQLException e) { return value.toString().trim(); }
switch(type)
{
case Types.BIGINT:
return val = new Long(value.toString().trim());
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
return val = new Integer(value.toString().trim());
case Types.REAL:
case Types.FLOAT:
case Types.DOUBLE:
case Types.NUMERIC:
return val = new Double(value.toString().trim());
default: return val = new String(value.toString());
}
}
//---------------------------------------------------------------------------------------
public void SqlReload(String query)
{
try{
resultSet=null;
fireTableDataChanged();
resultSet = statement.executeQuery(query);
if(resultSet == null){
con.rollback();
con.close();
return;
}
metaData = resultSet.getMetaData();
int numberOfColumns = metaData.getColumnCount();
columnNames = new String[numberOfColumns];
for(int column = 1; column <= numberOfColumns; column++)
{
columnNames[column-1] =
metaData.getColumnName(column);
}
rows = new Vector();
while (resultSet.next())
{
Vector newRow = new Vector();
for (int i = 1; i <= getColumnCount(); i++)
{
newRow.addElement(resultSet.getObject(i));
}
rows.addElement(newRow);
}
// resultSet.close();
// con.commit();
// con.close();
// statement.close();
fireTableDataChanged();
}
catch(SQLException e)
{
try {
con.close();
statement.close();
} catch (SQLException e1) {
// TODO Автоматически созданный блок catch
e1.printStackTrace();
}
System.out.println(e.getMessage());
}
}
//----------------------------------------------------------------------------------------
public void SqlInsert(String query,String query1)
{
try{
PreparedStatement pstmt =
con.prepareStatement(query);
pstmt.executeUpdate();
pstmt.close();
SqlReload(query1);
con.commit();
con.close();
statement.close();
}
catch(SQLException e)
{
try {
con.close();
statement.close();
} catch (SQLException e1) {
// TODO Автоматически созданный блок catch
e1.printStackTrace();
}
System.out.println(e.getMessage());
}
}
//******************************************************************************************
public void Sql(String query1)
{
try{
PreparedStatement pstmt =
con.prepareStatement(query1);
pstmt.executeUpdate();
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
}
//---------------------------------------------------------------------------------------
public void SqlDelete(String query,String query1,int cur_row)
{
try{
String query_=query;
query_= query_ + tbRepresentation(0, getValueAt(cur_row, 0));
fireTableDataChanged();
PreparedStatement pstmt = con.prepareStatement(query_);
System.out.println(query_);
pstmt.executeUpdate();
SqlReload(query1);
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
}
//---------------------------------------------------------------------------------------
public void fireTableRowsDeleted(int firstRow, int lastRow) {
}
public void fireTableRowsInserted(int firstRow, int lastRow) {
}
protected void finalize() throws Throwable {
// con.close();
resultSet.close();
con.commit();
con.close();
super.finalize();
}
//------------------------------------------------------
}
|
запись данных в текстовый файл | Код |
getDB1();
System.out.println("kod "+DB.getValueAt(jTable.getSelectedRow(),0).toString());
//System.out.println(DB1.getValueAt(0,0).toString().trim());
try{
PrintWriter writer = new PrintWriter(new OutputStreamWriter(new FileOutputStream("book.txt"),"windows-1251"));
writer.write("<?xml version='1.0' encoding='WINDOWS-1251' ?>\n"+DB1.getValueAt(0,0).toString().trim());
//writer.write(DB1.getValueAt(0,0).toString().trim());
writer.flush();
writer.close();
}catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
private database2 getDB1() {
//if (DB2 == null) {
DB1 = new database2(
"SELECT public.table2.xml_file FROM public.table2" ,
"examples", "postgres", "syrus", "mouth","","",new String[]{});
//}
return DB1;
}
|
копирую данные из текстового файла в xml документ | Код |
package library;
// This example is from _Java Examples in a Nutshell_. (http://www.oreilly.com)
// Copyright (c) 1997 by David Flanagan
// This example is provided WITHOUT ANY WARRANTY either expressed or implied.
// You may study, use, modify, and distribute it for non-commercial purposes.
// For any commercial use, see http://www.davidflanagan.com/javaexamples
import java.io.*;
/**
* This class is a standalone program to copy a file, and also defines a
* static copy() method that other programs can use to copy files.
**/
public class FileCopy {
/** The main() method of the standalone program. Calls copy(). */
public static void main(String[] args) {
if (args.length != 2) // Check arguments
System.err.println("Usage: java FileCopy <source file> <destination>");
else {
// Call copy() to do the copy, and display any error messages it throws.
try { copy(args[0], args[1]); }
catch (IOException e) { System.err.println(e.getMessage()); }
}
}
/**
* The static method that actually performs the file copy.
* Before copying the file, however, it performs a lot of tests to make
* sure everything is as it should be.
*/
public static void copy(String from_name, String to_name) throws IOException{
File from_file = new File(from_name); // Get File objects from Strings
File to_file = new File(to_name);
// First make sure the source file exists, is a file, and is readable.
if (!from_file.exists())
abort("FileCopy: no such source file: " + from_name);
if (!from_file.isFile())
abort("FileCopy: can't copy directory: " + from_name);
if (!from_file.canRead())
abort("FileCopy: source file is unreadable: " + from_name);
// If the destination is a directory, use the source file name
// as the destination file name
if (to_file.isDirectory())
to_file = new File(to_file, from_file.getName());
// If the destination exists, make sure it is a writeable file
// and ask before overwriting it. If the destination doesn't
// exist, make sure the directory exists and is writeable.
if (to_file.exists()) {
if (!to_file.canWrite())
abort("FileCopy: destination file is unwriteable: " + to_name);
// Ask whether to overwrite it
System.out.print("Overwrite existing file " + to_name + "? (Y/N): ");
System.out.flush();
// Get the user's response.
BufferedReader in=new BufferedReader(new InputStreamReader(System.in));
String response = in.readLine();
// Check the response. If not a Yes, abort the copy.
if (!response.equals("Y") && !response.equals("y"))
abort("FileCopy: existing file was not overwritten.");
}
else {
// if file doesn't exist, check if directory exists and is writeable.
// If getParent() returns null, then the directory is the current dir.
// so look up the user.dir system property to find out what that is.
String parent = to_file.getParent(); // Get the destination directory
if (parent == null) parent = System.getProperty("user.dir"); // or CWD
File dir = new File(parent); // Convert it to a file.
if (!dir.exists())
abort("FileCopy: destination directory doesn't exist: " + parent);
if (dir.isFile())
abort("FileCopy: destination is not a directory: " + parent);
if (!dir.canWrite())
abort("FileCopy: destination directory is unwriteable: " + parent);
}
// If we've gotten this far, then everything is okay.
// So we copy the file, a buffer of bytes at a time.
FileInputStream from = null; // Stream to read from source
FileOutputStream to = null; // Stream to write to destination
try {
from = new FileInputStream(from_file); // Create input stream
to = new FileOutputStream(to_file); // Create output stream
byte[] buffer = new byte[4096]; // A buffer to hold file contents
int bytes_read; // How many bytes in buffer
// Read a chunk of bytes into the buffer, then write them out,
// looping until we reach the end of the file (when read() returns -1).
// Note the combination of assignment and comparison in this while
// loop. This is a common I/O programming idiom.
while((bytes_read = from.read(buffer)) != -1) // Read bytes until EOF
to.write(buffer, 0, bytes_read); // write bytes
}
// Always close the streams, even if exceptions were thrown
finally {
if (from != null) try { from.close(); } catch (IOException e) { ; }
if (to != null) try { to.close(); } catch (IOException e) { ; }
}
}
/** A convenience method to throw an exception */
private static void abort(String msg) throws IOException {
throw new IOException(msg);
}
}
|
| Код |
File file = new File("book.xml");
if (file.exists()) file.delete();
try {
FileCopy.copy("book.txt","book.xml");
} catch (IOException e1) {
// TODO Автоматически созданный блок catch
e1.printStackTrace();
}
|
--------------------
Если новая программа с первого раза компилируется без ошибок, значит, она написана принципиально неправильно. |
Вывод данных из БД PostgreSQL, Xml отображенмие данных 
Загрузка ...
